sqwlite insert variable data help

[rclodfelter]

I'm trying to insert data from an edittext filed into a sqlite database. I defined and created a database called meds with one table called meds. the table has two cols both text. If I insert data strings in quotes it works but if I try a variable it fails. Below is my code. The error message I am getting says

No such column: EditText.Text: Insert into meds values(editText.text, 'aasdf')

Here is my code, any help is appreciated.

***************************************
Dim EditText1 As EditText
Dim SQL1 As SQL
SQL1.Initialize(File.DirInternal, "med.db", True)
SQL1.ExecNonQuery("DROP TABLE IF EXISTS meds")
SQL1.ExecNonQuery("CREATE TABLE meds (col1 TEXT , col2 TEXT)")


SQL1.ExecNonQuery("INSERT INTO meds VALUES(EditText1.Text, 'aasdf')")

 

[rclodfelter]

I tried this sql and it didn't error.

SQL1.ExecNonQuery2("INSERT INTO meds VALUES (?, ?)", Array As Object(EditText1.Text, hm))

I haven't had to query the table and prove the data got in there but I'd like to know why passing objects from an array would work while a simple insert didn't.

 

[Erel]

There is no variable in this string:

SQL1.ExecNonQuery("INSERT INTO meds VALUES(EditText1.Text, 'aasdf')")

The correct code is:

SQL1.ExecNonQuery("INSERT INTO meds VALUES(" & EditText1.Text & ", 'aasdf')")

Though your second solution is much better as you do not need to escape any illegal character.

 

[rclodfelter]

Thanks,

That worked. The second value was also a variable so I ended up with this.

SQL1.ExecNonQuery("INSERT INTO meds VALUES( ' & EditText1.Text & ', ' & hm & ')")

 

[Erel]

I think that your previous solution is actually better:

SQL1.ExecNonQuery2("INSERT INTO meds VALUES (?, ?)", Array As Object(EditText1.Text, hm))

 

[peacemaker]

how to be (replace quotes) in B2A long SQL INSERT\UPDATE request string, if this string is created dinamically, fields names and qty are variables (download by HTTP), and any value of them can be a string with quotes ? Fields values are also in double quotes for INSERT, and I mean that whole the big SQL string cannot be easy formatted as string with & QUOTE & parts.

B4A gives SQLite exception "sintax error". Quotes in the values must be saved\used.... :-( not to be replaced by two single quotes. What more symbols in the fields values must be specially checked (excepting destructive SQL injections...)?

 

[Erel]

See my answer in stackoverflow.com.

 

[mangojack]

I'm having the same problem inserting a variable to table

Dim strA As String
strA = "this is a test"
   
'write to db
SQL1.ExecNonQuery ("INSERT INTO myTable VALUES( ' & strA & ' )")

[end code]

the table gets populated with  "     & strA &      "
I have also played with the ExecNonQuery2 as in above example ... Am I missing something ????

Thanks   Cheers mj

 

[klaus]

It must be:
SQL1.ExecNonQuery ("INSERT INTO myTable VALUES( '" & strA & "' )")

Best regards.

 

[mangojack]

Many thanks Klaus ..

Cheers mj

 

[Sinan Tuzcu]

Helo,

can somboady help me,
why is this code wrong?

SQL1.ExecNonQuery("INSERT INTO nachrichten VALUES('" & ayir(0) & "'" & ayir(1) & "'" & ayir(2) & "'" & ayir(3) & "'" & ayir(4) & "')"  )

greet
sinan

 

[Mahares]

Use something like this:

SQL1.ExecNonQuery2("INSERT INTO nachrichten VALUES(?,?,?,?,?)", Array As Object(ayir(0), ayir(1), ayir(2), ayir(3),ayir(4) ))

 

[zeuspower]

I think the rigtht is to use array as ,look an example of mine thats works for me:

SQL2.ExecNonQuery2("INSERT INTO table2 VALUES(?,?,?,?,?,?,?,?)", Array As String(tempIdCounter2,edt_askisi.Text,edt_perioxi.Text,edt_sets.Text,edt_repeats.Text,edt_baros.Text,lbl_date_time.Text,number_askisis))

 

[Sinan Tuzcu]

Use something like this:

SQL1.ExecNonQuery2("INSERT INTO nachrichten VALUES(?,?,?,?,?)", Array As Object(ayir(0), ayir(1), ayir(2), ayir(3),ayir(4) ))

thanx this code works

 

[mangojack]

This works for me ...

Dim arrData(6) As String

For i = 0 to 5
   arrData(i) = "myString " & i
Next
      
   SQL1.ExecNonQuery2 ("INSERT INTO "& MyTable &" VALUES (NULL,?, ?, ?, ?, ?, ?)" , arrData)

Cheers mj