sqwlite insert variable data help

R

rclodfelter

Member
Licensed User
Longtime User
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')")
 
Sort by date Sort by votes
R

rclodfelter

Member
Licensed User
Longtime User
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.
 
Upvote 0
R

rclodfelter

Member
Licensed User
Longtime User
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 & ')")
 
Upvote 0

peacemaker

Expert
Licensed User
Longtime User
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...)?
 
Upvote 0

mangojack

Expert
Licensed User
Longtime User
I'm having the same problem inserting a variable to table

B4X: 
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
 
Upvote 0

mangojack

Expert
Licensed User
Longtime User
Many thanks Klaus ..

Cheers mj
 
Upvote 0

Sinan Tuzcu

Well-Known Member
Licensed User
Longtime User
Helo,

can somboady help me,
why is this code wrong?

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

greet
sinan
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Use something like this:
B4X: 
SQL1.ExecNonQuery2("INSERT INTO nachrichten VALUES(?,?,?,?,?)", Array As Object(ayir(0), ayir(1), ayir(2), ayir(3),ayir(4) ))
 
Upvote 0
Z

zeuspower

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


B4X: 
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))
 
Upvote 0

mangojack

Expert
Licensed User
Longtime User
This works for me ...
B4X: 
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
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…