(DESKTOP) Search Database

Databases
1

What im trying to do now is search my database (CustomerDB.sqlite) and search the Table (Invoice) for what ever date is entered in Textfield1.

here is what i have. im getting no error. just not displaying it in the listbox.
This is my GetDeadlineDisplay Method.

[code]
Dim sql as string
Dim rs as RecordSet

DeadlineSearchWin.Listbox1.DeleteAllRows

sql = “SELECT * FROM Invoice WHERE Deadline =” + DeadlineSearchWin.TextField1.text
rs = mDB.SQLSelect(sql)

if rs <> nil then
While Not rs.EOF
DeadlineSearchWin.Listbox1.AddRow(rs.Field(“Department”).StringValue, rs.Field(“InvoiceNumber”).StringValue, rs.Field(“DateCreated”).StringValue)

  rs.MoveNext
  
Wend

rs.close

end if[/code]

any ideas ? Examples ? Help ?

Thank you,
Travis McBride
Xojo-Experience Studio

2

Consider changing

sql = "SELECT * FROM Invoice WHERE Deadline =" + DeadlineSearchWin.TextField1.text

to

sql = "SELECT * FROM Invoice WHERE Deadline = '" + DeadlineSearchWin.TextField1.text + "'"

if Deadline is a a Text field.

3

First, refactor this code later to use a prepared statement instead. The way you’ve done it does not guard against SQL injections.

You haven’t checked mDB for an error after SQLSelect. Do that and see what, if anything, it tells you.

4

What I suggested may be a quick fix but as Kem pointed out, the code should be written quite differently in order for it to be safe and catch errors.

5

Here’s what that might look like:

  Dim sql as string
  Dim rs as RecordSet
  
  DeadlineSearchWin.Listbox1.DeleteAllRows
  
  sql = "SELECT * FROM Invoice WHERE Deadline = ?" 
  dim ps as PreparedSQLStatement = mDB.Prepare( sql )
  ps.BindType( 0, SQLitePreparedStatement.SQLITE_TEXT )
  rs = ps.SQLSelect( DeadlineSearchWin.TextField1.Text )
  
  if mDB.Error then
    // Deal with the error 
 
  elseif rs <> nil then
    While Not rs.EOF
      DeadlineSearchWin.Listbox1.AddRow(rs.Field("Department").StringValue, rs.Field("InvoiceNumber").StringValue, rs.Field("DateCreated").StringValue)
      
      rs.MoveNext
      
    Wend
    
    rs.close
  end if
6

Thank you all for the help!. Just a Quote mark mistake ! lol thank you so much. @Frederick Roller Your fix worked! and @Kem Tekinay does that secure it using your idea?

[quote=179167:@Frederick Roller]Consider changing

sql = "SELECT * FROM Invoice WHERE Deadline =" + DeadlineSearchWin.TextField1.text

to

sql = "SELECT * FROM Invoice WHERE Deadline = '" + DeadlineSearchWin.TextField1.text + "'"

if Deadline is a a Text field.[/quote]

7

Yes.