Syntax check please :)

Could someone please check my syntax - I believe the error lies on either line 2 or line 5

Thank you very much :slight_smile:

[code] // PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM Snippets WHERE Title LIKE ?;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

// BIND THE SEARCH STRING AND EXECUTE
ps.Bind(0, ‘%TextField1.text%’)
ps.SQLSelect

// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)

Else
// CLOSE THE WINDOW
SearchWindow.close

// UPDATE THE LISTBOX
MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle

End If[/code]

Maybe the bind?
ps.Bind(0, ‘%’ + TextField1.text + ‘%’)

Disclaimer: Haven’t looked at the docs or anything at all, just guessing.

I would bind to a String Variable instead of how you are trying to bind to the textfield.

dim BindString as String = textfield1.text
ps.Bind(0, BindString)
HTH

Tim - thanks - will try your code.

Mike - could you please explain the advantage, as I am interested in learning as much as possible :slight_smile:

Personally I don’t like hard coding user input fields directly into my SQL Statements, but I can’t say that it is wrong. I try to separate my code for example this method would include an input parameter where I would pass in my “Textfield.Text” info perhaps and then bind to that variable.

I did not expect to see ampersands (%) in your bind source so I was wondering if that is a source of your issue.

I thought % was the wildcard.

Tim - it was.
I will replace my code with yours :slight_smile:

Mike,
The % are wildcards :slight_smile:

Thank you both.

Thanks Guys as I never use wildcards too often.

OK - here is my updated code.

As I am using an SQLSelect - I added line 6, but I am now a bit lost at the final hurdle :frowning:
I know I need to add some code somewhere to get the recordset to work, but my mind has now gone completely blank (hard work - all this learning stuff) :slight_smile:

Can someone help me with what part of code I am missing - in order to search for a user entered string and then update my listbox?

THANK YOU!

[code] // PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM Snippets WHERE Title LIKE ?;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

// BIND THE SEARCH STRING AND EXECUTE
ps.Bind(0, ‘%’ + TextField1.text + ‘%’)
rs = ps.SQLSelect

// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)

Else
// CLOSE THE WINDOW
SearchWindow.close

// UPDATE THE LISTBOX
MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle

End If[/code]

Richard following up on my style preference here is a SQLInsert Method that I use. Notice the parameters I pass in to bind to:

Sub EggProdTable_SQLInsert(inDateCollected as String, inNumOfEggsCollected as integer, inWhoCollectedEggs as string, inNameOfHen as string, inNestingBoxID as string)
  Dim ThisDateIs as New Date
  Dim SQLInsertStatement As String =  "insert into EggProdTable (DateCollected,RecordDate,NumOfEggsCollected,WhoCollectedEggs,NameOfHenLaid,NestingBoxID) values (?, ?, ?, ?, ?, ?)"
  
  Dim prepInsert As SQLitePreparedStatement = SQLiteDB.Prepare(SQLInsertStatement)
  
  // Set Bind Mappings for DB Table Columns
  Const DateCollected = 0
  Const RecordDate = 1
  Const NumOfEggsCollected = 2
  Const WhoCollectedEggs = 3
  Const NameOfHenLaid = 4
  Const NestingBoxID = 5
  
  prepInsert.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  prepInsert.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
  prepInsert.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
  prepInsert.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
  prepInsert.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
  prepInsert.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)
  
  // Use Passed Values to Submit into our Table
  prepInsert.Bind(0,  inDateCollected)
  prepInsert.Bind(1, ThisDateIs.SQLDateTime)
  prepInsert.Bind(2,  inNumOfEggsCollected)
  prepInsert.Bind(3,  inWhoCollectedEggs)
  prepInsert.Bind(4,  NameOfHenLaid)
  prepInsert.Bind(5,  inNestingBoxID)
  
  // Execute Prepared SQL Statements
  prepInsert.SQLExecute
End Sub

Wow - bit beyond me at the moment - but I understand from the code that you like eggs??? :slight_smile: :slight_smile: :slight_smile:
Thanks anyway - I will study it more when I am more knowledgeable.

hahaha :slight_smile: Chickens man Chickens :slight_smile: hehe

I am creating the first “BackYard Chicken Farming” Cloud / Desktop / Mobile" service :slight_smile: This is from my desktop version :slight_smile:

basically what I was just trying to show is how I bound my input passed parameters to keep things a bit more clean.

Mike
You do not show any error checking after the SQLExecute. Should there be one?

[quote=92060:@Jim Smith]Mike
You do not show any error checking after the SQLExecute. Should there be one?[/quote]

I do but I didn’t paste that in as it was very long :wink:

Thanks Jim!!!

[quote=92063:@Mike Cotrone]I do but I didn’t paste that in as it was very long :wink:

Thanks Jim!!![/quote]

Also I saw they had that above in Richard’s original snippet.

I am still lost as to what code is missing in my last code review??

Mike I’m wondering why you don’t use the constants?

E.g.

[code]Sub EggProdTable_SQLInsert(inDateCollected as String, inNumOfEggsCollected as integer, inWhoCollectedEggs as string, inNameOfHen as string, inNestingBoxID as string)
Dim ThisDateIs as New Date
Dim SQLInsertStatement As String = “insert into EggProdTable (DateCollected,RecordDate,NumOfEggsCollected,WhoCollectedEggs,NameOfHenLaid,NestingBoxID) values (?, ?, ?, ?, ?, ?)”

Dim prepInsert As SQLitePreparedStatement = SQLiteDB.Prepare(SQLInsertStatement)

// Set Bind Mappings for DB Table Columns
Const DateCollected = 0
Const RecordDate = 1
Const NumOfEggsCollected = 2
Const WhoCollectedEggs = 3
Const NameOfHenLaid = 4
Const NestingBoxID = 5

prepInsert.BindType(DateCollected, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(RecordDate, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(NumOfEggsCollected, SQLitePreparedStatement.SQLITE_INTEGER)
prepInsert.BindType(WhoCollectedEggs, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(NameOfHen, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(NestingBoxID, SQLitePreparedStatement.SQLITE_TEXT)

// Use Passed Values to Submit into our Table
prepInsert.Bind(DateCollected, inDateCollected)
prepInsert.Bind(RecordDate, ThisDateIs.SQLDateTime)
prepInsert.Bind(NumOfEggsCollected, inNumOfEggsCollected)
prepInsert.Bind(WhoCollectedEggs, inWhoCollectedEggs)
prepInsert.Bind(NameOfHenLaid, idNameOfHen)
prepInsert.Bind(NestingBoxID, inNestingBoxID)

// Execute Prepared SQL Statements
prepInsert.SQLExecute
End Sub[/code]

Richard

If it was me I’d move the %'s to the sql string %?% will work & I think is more readable.

Ok,
so my code NOW looks like this ??

[code]// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM Snippets WHERE Title LIKE %?%;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

// BIND THE SEARCH STRING AND EXECUTE
ps.Bind(0, TextField1.text)
rs = ps.SQLSelect

// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)

Else
// CLOSE THE WINDOW
SearchWindow.close

// UPDATE THE LISTBOX
MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle

End If[/code]

Does it work?