how to insert many columns in sqlite.

[hessu]

Code: Select all

#Separator = ","
Enumeration
#ruoka_Livsmedelsnamn
#ruoka_Livsmedelsnummer
#ruoka_Energi_kcal 
#ruoka_Energi_kJ
#ruoka_Kolhydrater_g
#ruoka_Fett_g
#ruoka_Protein_g
#ruoka_Fibrer_g
#ruoka_Vatten_g 
#ruoka_Alkohol_g 
#ruoka_Aska_g 
#ruoka_Monosackarider_g 
#ruoka_Disackarider_g 
#ruoka_Sackaros_g 
#ruoka_Fullkorn_totalt_g 
#ruoka_Sockerarter_g
#ruoka_Summa_mattade_fettsyror_g
#ruoka_Fettsyra_4_0_10_0_g 
#ruoka_Fettsyra_12_0_g 
#ruoka_Fettsyra_14_0_g
#ruoka_Fettsyra_16_0_g 
#ruoka_Fettsyra_18_0_g 
#ruoka_Fettsyra_20_0_g
#ruoka_Summa_enkelomattade_fettsyror_g 
#ruoka_Fettsyra_16_1_g 
#ruoka_Fettsyra_18_1_g
#ruoka_Summa_fleromattade_fettsyror_g 
#ruoka_Fettsyra_18_2_g 
#ruoka_Fettsyra_18_3_g
#ruoka_Fettsyra_20_4_g 
#ruoka_EPA_Fettsyra_20_5_g
#ruoka_DPA_Fettsyra_22_5_g
#ruoka_DHA_Fettsyra_22_6_g
#ruoka_Kolesterol_mg
#ruoka_Retinol_ug
#ruoka_Vitamin_A_ug
#ruoka_hmm_Karoten_ug
#ruoka_Vitamin_D_ug
#ruoka_Vitamin_E_mg
#ruoka_Vitamin_K_ug
#ruoka_Tiamin_mg
#ruoka_Riboflavin_mg 
#ruoka_Vitamin_C_mg
#ruoka_Niacin_mg 
#ruoka_Niacinekvivalenter_mg
#ruoka_Vitamin_B6_mg 
#ruoka_Vitamin_B12_ug 
#ruoka_Folat_ug 
#ruoka_Fosfor_mg
#ruoka_Jod_ug 
#ruoka_Jarn_mg 
#ruoka_Kalcium_mg
#ruoka_Kalium_mg 
#ruoka_Magnesium_mg
#ruoka_Natrium_mg
#ruoka_Salt_g
#ruoka_Selen_ug
#ruoka_Zink_mg
#ruoka_Avfall_skal_etc_prosent 


EndEnumeration

UseSQLiteDatabase() 

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

;If Filename$
  File = ReadFile(#PB_Any, Filename$)
  ;If File
  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)




      
DatabaseUpdate(1, "INSERT INTO ruoka (Livsmedelsnamn,Livsmedelsnummer,Energi_kcal,Energi_kJ,Kolhydrater_g,Fett_g,Protein_g) VALUES ('"+Livsmedelsnamn$+"','"+Livsmedelsnummer$+"','"+Energi_kcal$+"','"+Energi_kJ$+"','"+Kolhydrater_g$+"','"+Fett_g$+"','"+Protein_g$+"' );")
DatabaseUpdate(1, "INSERT INTO ruoka (Fibrer_g,Vatten_g,Alkohol_g,Aska_g,Monosackarider_g) VALUES ,'"+Fibrer_g$+"','"+Vatten_g$+"','"+Alkohol_g$+"','"+Aska_g$+"','"+Monosackarider_g$+"' ) ;")
DatabaseUpdate(1, "INSERT INTO ruoka (Disackarider_g,Sackaros_g,Fullkorn_totalt_g,Sockerarter_g,Summa_mattade_fettsyror_g,Fettsyra_4_0_10_0_g) VALUES ('"+Disackarider_g$+"','"+Sackaros_g$+"','"+Fullkorn_totalt_g$+"','"+Sockerarter_g$+'","'+Summa_mattade_fettsyror_g$+"','"+Fettsyra_4_0_10_0_g$+"' );")
DatabaseUpdate(1, "INSERT INTO ruoka (Fettsyra_12_0_g,Fettsyra_14_0_g,Fettsyra_16_0_g,Fettsyra_18_0_g,Fettsyra_20_0_g,Summa_enkelomattade_fettsyror_g) VALUES ('"+Fettsyra_12_0_g$+"','"+Fettsyra_14_0_g$+"','"+Fettsyra_16_0_g$+"','"+Fettsyra_18_0_g$+"', '"+Fettsyra_20_0_g$+"','"+Summa_enkelomattade_fettsyror_g$+"' );")
DatabaseUpdate(1, "INSERT INTO ruoka (Fettsyra_16_1_g,Fettsyra_18_1_g,Summa_fleromattade_fettsyror_g,Fettsyra_18_2_g,Fettsyra_18_3_g,Fettsyra_20_4_g) VALUES ('"+Fettsyra_16_1_g$+"','"+Fettsyra_18_1_g$+"','"+Summa_fleromattade_fettsyror_g$+"','"+Fettsyra_18_2_g$+"','"+Fettsyra_18_3_g$+"','"+Fettsyra_20_4_g$+"' );")
DatabaseUpdate(1, "INSERT INTO ruoka (EPA_Fettsyra_20_5_g,DPA_Fettsyra_22_5_g) VALUES ('"+EPA_Fettsyra_20_5_g$+"','"+DPA_Fettsyra_22_5_g$+"');")
DatabaseUpdate(1, "INSERT INTO ruoka (DHA_Fettsyra_22_6_g,Kolesterol_mg,Retinol_ug,Vitamin_A_ug,hmm_Karoten_ug,Vitamin_D_ug) VALUES ('"+DHA_Fettsyra_22_6_g$+"','"+Kolesterol_mg$+"','"+Retinol_ug$+"','"+Vitamin_A_ug$+"','"+hmm_Karoten_ug$+"','"+Vitamin_D_ug$+"');")
DatabaseUpdate(1, "INSERT INTO ruoka (Vitamin_E_mg,Vitamin_K_ug,Tiamin_mg,Riboflavin_mg,Vitamin_C_mg,Niacin_mg,Niacinekvivalenter_mg,Vitamin_B6_mg,Vitamin_B12_ug) VALUES ('"+Vitamin_E_mg$+"','"+Vitamin_K_ug$+"','"+Tiamin_mg$+"','"+Riboflavin_mg$+"','"+Vitamin_C_mg$+"','"+Niacin_mg$+"','"+Niacinekvivalenter_mg$+"','"+Vitamin_B6_mg$+"','"+Vitamin_B12_ug$+"');")
DatabaseUpdate(1, "INSERT INTO ruoka (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) VALUES ('"+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$+"');")

Sorry, this is swedish language. (food database)
I have several fields. If I run this, next record Starts with (Fibrer_g,Vatten_g,Alkohol_g,Aska_g,Monosackarider_g)
and next (Disackarider_g,Sackaros_g,Fullkorn_totalt_g,Sockerarter_g,Summa_mattade_fettsyror_g,Fettsyra_4_0_10_0_g) and so on.
My questio is: how to link these DatabaseUpdate(1, "INSERT INTO rows so I get whole records. I have no idea. I have found no examples.
I've tried and tried but no success.
please help me.

hessu

[Keya]

hello i cant help you with SQL sorry but it struck me that you were using so many individual string variables for the same data, especially when you already had an Enumeration in place to use them in a string array, but didnt seem to be using them!

for example rather than:

Code: Select all

Tiamin_mg$=StringField(Line$, 41, #Separator)
Riboflavin_mg$=StringField(Line$, 42, #Separator)
Vitamin_C_mg$=StringField(Line$, 43, #Separator)... {etc}

You already have #ruoka_Tiamin_mg, #ruoka_Riboflavin_mg, and #ruoka_Vitamin_C_mg in the Enum corresponding to 41, 42, 43 respectively,
so how about:

Code: Select all

Dim Product$(#ruoka_Avfall_skal_etc_prosent+1)  ;last item in the Enum
Product$(#ruoka_Tiamin_mg)=StringField(Line$, #ruoka_Tiamin_mg, #Separator)
Product$(#ruoka_Riboflavin_mg)=StringField(Line$, #ruoka_Riboflavin_mg, #Separator)
Product$(#ruoka_Vitamin_C_mg)=StringField(Line$, #ruoka_Vitamin_C_mg, #Separator)

and then because you can access them by array you no longer need to do them all individually either, so to fill the entire array you can simply:

Code: Select all

For i = #ruoka_Livsmedelsnamn To #ruoka_Avfall_skal_etc_prosent   ;first to last values in the Enum
 Product$(i) =  StringField(Line$, i, #Separator)
Next i

[Bisonte]

You have to collect all your SQL Statements to one string.

Like this... :

Code: Select all

SQL.s = "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)

BTW, this is the wrong forum ... please a mod can move it to the right section

[hessu]

Hi Bisonte.

Thanks for this, unfortunately it's insert nothing. But I got good advice anyway.

hessu

Now I'll try get this moved to right place.

[infratec]

Hi,

then there is an error in the SQL$.

Use this:

Code: Select all

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

Bernd

[hessu]

Yeah. There were errors.

58 values for 59 columns

I have read every thing in code, but it's so difficult see everythig.

Thank you so much.
Maybe I'll get it work.

I found it. There was '",'" this error. While I counted these values I saw it has another color.