Getting double records

Just starting out? Need help? Post your questions and find answers here.
Post Reply
hessu
User
User
Posts: 25
Joined: Fri Nov 20, 2015 6:30 am

Getting double records

Post by hessu »

Code: Select all

UseSQLiteDatabase() 

CreateFile(1,GetCurrentDirectory() + "Ruoka.sqlite")
CloseFile(1)


OpenDatabase(1,GetCurrentDirectory() + "ruoka.sqlite","","",#PB_Database_SQLite) 
 
; 
DatabaseUpdate(1, "CREATE TABLE Ruoka ( Livsmedelsnamn TEXT ) ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Livsmedelsnummer SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Energi_kcal SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Energi_kJ SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Kolhydrater_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fett_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Protein_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fibrer_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Vatten_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Alkohol_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Aska_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Monosackarider_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Disackarider_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Sackaros_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fullkorn_totalt_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Sockerarter_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Summa_mattade_fettsyror_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_4_0_10_0_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_12_0_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_14_0_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_16_0_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_18_0_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_20_0_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Summa_enkelomattade_fettsyror_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_16_1_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_18_1_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Summa_fleromattade_fettsyror_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_18_2_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_18_3_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fettsyra_20_4_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD EPA_Fettsyra_20_5_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD DPA_Fettsyra_22_5_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD DHA_Fettsyra_22_6_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Kolesterol_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Retinol_ug SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Vitamin_A_ug SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD hmm_Karoten_ug SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Vitamin_D_ug SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Vitamin_E_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Vitamin_K_ug SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Tiamin_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Riboflavin_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Vitamin_C_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Niacin_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Niacinekvivalenter_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Vitamin_B6_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Vitamin_B12_ug SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Folat_ug SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Fosfor_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Jod_ug SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Jarn_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Kalcium_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Kalium_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Magnesium_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Natrium_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Salt_g SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Selen_ug SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Zink_mg SINGLE ;" ) 
DatabaseUpdate(1, "ALTER TABLE Ruoka ADD Avfall_skal_etc_prosent SINGLE ;" ) 

CloseDatabase(1)
I use this to create a new table.

Then this to insert new record to this table

Code: Select all

#Fields = 59
#Separator = ","
Enumeration
#Livsmedelsnamn
#Livsmedelsnummer
#Energi_kcal 
#Energi_kJ
#Kolhydrater_g
#Fett_g
#Protein_g
#Fibrer_g
#Vatten_g 
#Alkohol_g 
#Aska_g 
#Monosackarider_g 
#Disackarider_g 
#Sackaros_g 
#Fullkorn_totalt_g 
#Sockerarter_g
#Summa_mattade_fettsyror_g
#Fettsyra_4_0_10_0_g 
#Fettsyra_12_0_g 
#Fettsyra_14_0_g
#Fettsyra_16_0_g 
#Fettsyra_18_0_g 
#Fettsyra_20_0_g
#Summa_enkelomattade_fettsyror_g 
#Fettsyra_16_1_g 
#Fettsyra_18_1_g
#Summa_fleromattade_fettsyror_g 
#Fettsyra_18_2_g 
#Fettsyra_18_3_g
#Fettsyra_20_4_g 
#EPA_Fettsyra_20_5_g
#DPA_Fettsyra_22_5_g
#DHA_Fettsyra_22_6_g
#Kolesterol_mg
#Retinol_ug
#Vitamin_A_ug
#hmm_Karoten_ug
#Vitamin_D_ug
#Vitamin_E_mg
#Vitamin_K_ug
#Tiamin_mg
#Riboflavin_mg 
#Vitamin_C_mg
#Niacin_mg 
#Niacinekvivalenter_mg
#Vitamin_B6_mg 
#Vitamin_B12_ug 
#Folat_ug 
#Fosfor_mg
#Jod_ug 
#Jarn_mg 
#Kalcium_mg
#Kalium_mg 
#Magnesium_mg
#Natrium_mg
#Salt_g
#Selen_ug
#Zink_mg
#Avfall_skal_etc_prosent 


EndEnumeration

UseSQLiteDatabase() 

OpenDatabase(1, GetCurrentDirectory() + "ruoka.SQLite", "", "", #PB_Database_SQLite) 
Filename$ = OpenFileRequester("Choose a txt File", "", "*.txt|*.txt", 0)

  File = ReadFile(#PB_Any, Filename$)
  While Not Eof(File)
    
    Line$ = ReadString(File)
  
Livsmedelsnamn$=StringField(Line$, 1, #Separator)
Livsmedelsnummer$=StringField(Line$, 2, #Separator)
Energi_kcal$=StringField(Line$, 3, #Separator)
Energi_kJ$=StringField(Line$, 4, #Separator)
Kolhydrater_g$=StringField(Line$, 5, #Separator)
Fett_g$=StringField(Line$, 6, #Separator)
Protein_g$=StringField(Line$, 7, #Separator)
Fibrer_g$=StringField(Line$, 8, #Separator)
Vatten_g$=StringField(Line$, 9, #Separator)
Alkohol_g$=StringField(Line$, 10, #Separator)
Aska_g$=StringField(Line$, 11, #Separator)
Monosackarider_g$=StringField(Line$, 12, #Separator)
Disackarider_g$=StringField(Line$, 13, #Separator)
Sackaros_g$=StringField(Line$, 14, #Separator)
Fullkorn_totalt_g$=StringField(Line$, 15, #Separator)
Sockerarter_g$=StringField(Line$, 16, #Separator)
Summa_mattade_fettsyror_g$=StringField(Line$, 17, #Separator)
Fettsyra_4_0_10_0_g$=StringField(Line$, 18, #Separator)
Fettsyra_12_0_g$=StringField(Line$, 19, #Separator)
Fettsyra_14_0_g$=StringField(Line$, 20,#Separator)
Fettsyra_16_0_g$=StringField(Line$, 21, #Separator)
Fettsyra_18_0_g$=StringField(Line$, 22, #Separator) 
Fettsyra_20_0_g$=StringField(Line$, 23, #Separator)
Summa_enkelomattade_fettsyror_g$=StringField(Line$, 24, #Separator) 
Fettsyra_16_1_g$=StringField(Line$, 25, #Separator)
Fettsyra_18_1_g$=StringField(Line$, 26, #Separator)
Summa_fleromattade_fettsyror_g$=StringField(Line$, 27, #Separator)
Fettsyra_18_2_g$=StringField(Line$, 28, #Separator)
Fettsyra_18_3_g$=StringField(Line$, 29, #Separator)
Fettsyra_20_4_g$=StringField(Line$, 30, #Separator)
EPA_Fettsyra_20_5_g$=StringField(Line$, 31, #Separator)
DPA_Fettsyra_22_5_g$=StringField(Line$, 32, #Separator)
DHA_Fettsyra_22_6_g$=StringField(Line$, 33, #Separator)
Kolesterol_mg$=StringField(Line$, 34, #Separator)
Retinol_ug$=StringField(Line$, 35, #Separator)
Vitamin_A_ug$=StringField(Line$, 36, #Separator)
hmm_Karoten_ug$=StringField(Line$, 37, #Separator)
Vitamin_D_ug$=StringField(Line$, 38, #Separator)
Vitamin_E_mg$=StringField(Line$, 39, #Separator)
Vitamin_K_ug$=StringField(Line$, 40, #Separator)
Tiamin_mg$=StringField(Line$, 41, #Separator)
Riboflavin_mg$=StringField(Line$, 42, #Separator)
Vitamin_C_mg$=StringField(Line$, 43, #Separator)
Niacin_mg$=StringField(Line$, 44, #Separator)
Niacinekvivalenter_mg$=StringField(Line$, 45, #Separator)
Vitamin_B6_mg$=StringField(Line$, 46, #Separator)
Vitamin_B12_ug$=StringField(Line$, 47, #Separator)
Folat_ug$=StringField(Line$, 48, #Separator)
Fosfor_mg$=StringField(Line$, 49, #Separator)
Jod_ug$=StringField(Line$, 50, #Separator)
Jarn_mg$=StringField(Line$, 51, #Separator)
Kalcium_mg$=StringField(Line$, 52, #Separator)
Kalium_mg$=StringField(Line$, 53, #Separator)
Magnesium_mg$=StringField(Line$, 54, #Separator)
Natrium_mg$=StringField(Line$, 55, #Separator)
Salt_g$=StringField(Line$, 56, #Separator)
Selen_ug$=StringField(Line$, 57, #Separator)
Zink_mg$=StringField(Line$, 58, #Separator)
Avfall_skal_etc_prosent$=StringField(Line$, 59, #Separator)


SQL$ = "INSERT INTO ruoka (Livsmedelsnamn,Livsmedelsnummer,Energi_kcal,Energi_kJ,Kolhydrater_g,Fett_g,Protein_g,"
SQL$ + "Fibrer_g,Vatten_g,Alkohol_g,Aska_g,Monosackarider_g,Disackarider_g,Sackaros_g,Fullkorn_totalt_g,"
SQL$   + "Sockerarter_g,Summa_mattade_fettsyror_g,Fettsyra_4_0_10_0_g,Fettsyra_12_0_g,Fettsyra_14_0_g,Fettsyra_16_0_g," 
SQL$  + "Fettsyra_18_0_g,Fettsyra_20_0_g,Summa_enkelomattade_fettsyror_g,Fettsyra_16_1_g,Fettsyra_18_1_g,"
SQL$  + "Summa_fleromattade_fettsyror_g,Fettsyra_18_2_g,Fettsyra_18_3_g,Fettsyra_20_4_g,EPA_Fettsyra_20_5_g,"
SQL$  + "DPA_Fettsyra_22_5_g,DHA_Fettsyra_22_6_g,Kolesterol_mg,Retinol_ug,Vitamin_A_ug,hmm_Karoten_ug,Vitamin_D_ug,"
SQL$  + "Vitamin_E_mg,Vitamin_K_ug,Tiamin_mg,Riboflavin_mg,Vitamin_C_mg,Niacin_mg,Niacinekvivalenter_mg,Vitamin_B6_mg,"
SQL$  + "Vitamin_B12_ug,Folat_ug,Fosfor_mg,Jod_ug,Jarn_mg,Kalcium_mg,Kalium_mg,Magnesium_mg,Natrium_mg,Salt_g,Selen_ug,"
SQL$  + "Zink_mg,Avfall_skal_etc_prosent) VALUES ("
SQL$ + "'"+Livsmedelsnamn$+"','"+Livsmedelsnummer$+"','"+Energi_kcal$+"','"+Energi_kJ$+"','"+Kolhydrater_g$+"','"+Fett_g$+"','"+Protein_g$+"',"
SQL$ + "'"+Fibrer_g$+"','"+Vatten_g$+"','"+Alkohol_g$+"','"+Aska_g$+"','"+Monosackarider_g$+"',"
SQL$ + "'"+Disackarider_g$+"','"+Sackaros_g$+"','"+Fullkorn_totalt_g$+"','"+Sockerarter_g$+"','"+Summa_mattade_fettsyror_g$+"','"+Fettsyra_4_0_10_0_g$+"',"
SQL$ + "'"+Fettsyra_12_0_g$+"','"+Fettsyra_14_0_g$+"','"+Fettsyra_16_0_g$+"','"+Fettsyra_18_0_g$+"', '"+Fettsyra_20_0_g$+"','"+Summa_enkelomattade_fettsyror_g$+"',"
SQL$ + "'"+Fettsyra_16_1_g$+"','"+Fettsyra_18_1_g$+"','"+Summa_fleromattade_fettsyror_g$+"','"+Fettsyra_18_2_g$+"','"+Fettsyra_18_3_g$+"','"+Fettsyra_20_4_g$+"',"
SQL$ + "'"+EPA_Fettsyra_20_5_g$+"','"+DPA_Fettsyra_22_5_g$+"',"
SQL$ + "'"+DHA_Fettsyra_22_6_g$+"','"+Kolesterol_mg$+"','"+Retinol_ug$+"','"+Vitamin_A_ug$+"','"+hmm_Karoten_ug$+"','"+Vitamin_D_ug$+"',"
SQL$ + "'"+Vitamin_E_mg$+"','"+Vitamin_K_ug$+"','"+Tiamin_mg$+"','"+Riboflavin_mg$+"','"+Vitamin_C_mg$+"','"+Niacin_mg$+"','"+Niacinekvivalenter_mg$+"','"+Vitamin_B6_mg$+"','"+Vitamin_B12_ug$+"',"
SQL$ + "'"+Folat_ug$+"','"+Fosfor_mg$+"','"+Jod_ug$+"','"+Jarn_mg$+"','"+Kalcium_mg$+"','"+Kalium_mg$+"','"+Magnesium_mg$+"','"+Natrium_mg$+"','"+Salt_g$+"','"+Selen_ug$+"','"+Zink_mg$+"','"+Avfall_skal_etc_prosent$+"')"

DatabaseUpdate(1, SQL$)

If DatabaseUpdate(1, SQL$) = 0
  Debug SQL$
  Debug DatabaseError()
EndIf


  Wend
    
    CloseFile(File)
    CloseDatabase(1)
    
    
     OpenDatabase(1, GetCurrentDirectory() + "ruoka.SQLite", "", "", #PB_Database_SQLite) 
 
    DatabaseQuery(1, "SELECT * FROM ruoka " ) 

    
    While NextDatabaseRow(1) > 0 
      l=l+1
  x.s = GetDatabaseString(1,#Livsmedelsnamn) + "," 
  x.s = x.s + GetDatabaseString(1,#Energi_kcal) + "," 
  x.s = x.s + GetDatabaseString(1,#Kolhydrater_g)
  Debug x.s 
Wend 
Debug l
FinishDatabaseQuery(1) 
CloseDatabase(1)
Everything seems go ok, but I get every record twice.
Why this?????

hessu.
Top
User avatar
Kiffi
Addict
Addict
Posts: 1485
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: Getting double records

Post by Kiffi »

hessu wrote:Why this?????
because of this:
hessu wrote:

Code: Select all

[...]
DatabaseUpdate(1, SQL$)

If DatabaseUpdate(1, SQL$) = 0
[...]
Greetings ... Peter
Hygge
Top
IdeasVacuum
Always Here
Always Here
Posts: 6426
Joined: Fri Oct 23, 2009 2:33 am
Location: Wales, UK
Contact:
Contact IdeasVacuum

Re: Getting double records

Post by IdeasVacuum »

Instead of

Code: Select all

DatabaseUpdate(1, SQL$)

If DatabaseUpdate(1, SQL$) = 0
  Debug SQL$
  Debug DatabaseError()
EndIf
Use

Code: Select all

Result = DatabaseUpdate(1, SQL$)

If Result = 0
  Debug SQL$
  Debug DatabaseError()
EndIf
IdeasVacuum
If it sounds simple, you have not grasped the complexity.
Top
hessu
User
User
Posts: 25
Joined: Fri Nov 20, 2015 6:30 am

Re: Getting double records

Post by hessu »

Wow. Thank you so much.
No double records anymore.
Top
Post Reply

Return to “Coding Questions”