Android Question sqlite read data from database

[hamedaz]

Hi you dear friends
I've written this code. I want to get information from the database based on the following conditions. But I am faced with the following error message.

android.database.sqlite.SQLiteException: near "of": syntax error (code 1): , while compiling: SELECT * FROM idom WHERE name=Horse of a different color

b="Horse of a different color"
cur1=sql1.ExecQuery("SELECT * FROM idom WHERE name="&b)
For i=0 To cur1.RowCount -1
lbl.Initialize("")
        'lbl.Gravity=Gravity.CENTER_HORIZONTAL
        lbl.Gravity= Bit.Or(Gravity.CENTER_HORIZONTAL,Gravity.FILL)

        lbl.TextSize=20
        lbl.TextColor=Colors.Black
        lbl.Text=cur1.GetString("text")
next

 

[Erel]

Always prefer parameterized queries:

cur1 = sql1.ExecQuery2("SELECT * FROM ido WHERE name = ?" ,Array As String(b))

 

[hamedaz]

Always prefer parameterized queries:

cur1 = sql1.ExecQuery2("SELECT * FROM ido WHERE name = ?" ,Array As String(b))

I've used this code, I do not get an error message. But empty information from the database will show me

 

[Erel]

This means that there is no record where the name field equals to "Horse of a different color".

 

[hamedaz]

This means that there is no record where the name field equals to "Horse of a different color".

But the amount of data available in table

 

[Erel]

What is the output of:

Dim rs As ResultSet = sql.ExecQuery("SELECT name FROM ido")
Do While rs.NextRow
   Log(rs.GetString("name"))
Loop
rs.Close

 

[hamedaz]

What is the output of:

Dim rs As ResultSet = sql.ExecQuery("SELECT name FROM ido")
Do While rs.NextRow
   Log(rs.GetString("name"))
Loop
rs.Close

The output is as follows:
Horse of a different color
Horse of a different color

 

[mangojack]

@Erel ... slightly confused . any difference between ResultSet and Cursor in B4A / B4j. ?

@hamedaz .. try

b="Horse of a different color"
    cur1 = SQL1.ExecQuery2("SELECT * FROM idom WHERE name = ?" ,Array As String(b))  '?? table name = idom / ido
   
    Log (cur1.RowCount)   
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i   
        log(cur1.GetString("name"))    '?? there is a Column / field =  "text"  or "name"    
    Next

 

[hamedaz]

@Erel ... slightly confused . any difference between ResultSet and Cursor in B4A / B4j. ?

@hamedaz .. try

b="Horse of a different color"
    cur1 = SQL1.ExecQuery2("SELECT * FROM idom WHERE name = ?" ,Array As String(b))  '?? table name = idom / ido
  
    Log (cur1.RowCount)  
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i  
        log(cur1.GetString("name"))    '?? there is a Column / field =  "text"  or "name"   
    Next

I'm writing code in b4a
You wrote commands output were as follows:
0

 

[Erel]

any difference between ResultSet and Cursor in B4A / B4j. ?

ResultSet is a small extension to Cursor. The advantage of code based on ResultSet is that it is compatible with B4i and B4J.

@hamedaz please upload the database file.

 

[mangojack]

are you able to upload you db ?

 

[hamedaz]

ResultSet is a small extension to Cursor. The advantage of code based on ResultSet is that it is compatible with B4i and B4J.

@hamedaz please upload the database file.

This link database files were
http://s7.picofile.com/d/1e0f20ea-1787-4307-ac36-0dbfd9d08fe4/s.rar

http://expirebox.com/download/da597a806c0a9411a132ba790e849202.html

 

[hamedaz]

are you able to upload you db ?

apload file:
http://expirebox.com/download/da597a806c0a9411a132ba790e849202.html

 

[mangojack]

Your db is good .. try

Dim b as string ="Horse of a different color"
 cur1 = SQL1.ExecQuery2("SELECT * FROM idom WHERE name = ?" ,Array As String(b)) 
 Log (cur1.RowCount)  'Should = 2

For i = 0 To cur1.RowCount - 1
  cur1.Position = i 
  Log(cur1.GetString("text"))
Next

 

[hamedaz]

Your db is good .. try

Dim b as string ="Horse of a different color"
cur1 = SQL1.ExecQuery2("SELECT * FROM idom WHERE name = ?" ,Array As String(b))
Log (cur1.RowCount)  'Should = 2

For i = 0 To cur1.RowCount - 1
  cur1.Position = i
  Log(cur1.GetString("text"))
Next

I get the following output again:
0

 

[mangojack]

I get the following output again: 0

Can you upload project IDE > File > Export as Zip ... or paste a segment of code .

 

[hamedaz]

Can you upload project IDE > File > Export as Zip ... or paste a segment of code .

I uploaded my project:
http://expirebox.com/download/551c470906d38bc4f3a1ee15eb4b1515.html

 

[fixit30]

Sound's to me like you have an old/empty copy of the database on your device.

Try uninstalling the app and installing a fresh copy and try again.

 

[hamedaz]

Sound's to me like you have an old/empty copy of the database on your device.

Try uninstalling the app and installing a fresh copy and try again.

Sound's to me like you have an old/empty copy of the database on your device.

Try uninstalling the app and installing a fresh copy and try again.

I am using the simulator

 

[fixit30]

Hi Hamedaz.

The problem is with your database file. It contains two carriage returns after the text, and therefore is not an exact match for your search text.

Try the corrected database attached, or change your code to:

b = "%Horse of a different color%"
cur1=sql1.ExecQuery2("SELECT * FROM idom WHERE name Like ?", Array As String(b))