MSSQL Prepared Statements in Web App

Hello All,

I am struggling with a small bit of a prepared statement.
When I run the following bit of code I don’t get any errors, but I don’t get any records and I KNOW that I should:

[code]Dim db as MSSQLServerDatabase
Dim rs as RecordSet
Dim ps As MSSQLServerPreparedStatement

//DB connection stuff…

ps = db.Prepare(“SELECT * FROM dbo.TABLE_NAME WHERE COLUMN_NAME LIKE ‘%?%’”)
ps.BindType(0,MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)

rs = ps.SQLSelect(trim(textfield1.Text))[/code]

Any ideas where I am going wrong?

Thanks.

Since a Prepared Statement is doing the substations for you, enclosing the placeholder in quotes will not work. Instead, try this:

ps = db.Prepare("SELECT * FROM dbo.TABLE_NAME WHERE COLUMN_NAME LIKE ?")
ps.BindType(0,MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)

rs = ps.SQLSelect("%" + trim(textfield1.Text) + "%")

That makes sense, but it is returning a Nil recordset.
I tried it a couple ways:

rs = ps.SQLSelect("%" + trim(textfield1.Text) + "%") rs = ps.SQLSelect(textfield1.Text + "%") //removed trim in case that was throwing it off for some odd reason rs = ps.SQLSelect("'%" + trim(textfield1.Text) + "%'") //included single quotes in case that was causing issues

Nada.

Interestingly when I look at the database object in debug it shows:

Error = False ErrorCode = 0 ErrorMessage="Accessor is invalid."

The error message only populates AFTER the rs = ps… line.

Thoughts?

ps = MSSQLPreparedStatement(db.Prepare("SELECT * FROM dbo.TABLE_NAME WHERE COLUMN_NAME LIKE ?"))
ps.BindType(0,MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.Bind(0, "%"+ trim(textfield1.text)+"%")

rs = ps.SQLSelect()

Does this do any difference?

Hi Albin,

That was the trick! I guess you can’t do that implicitly like the instructions say you cant.

Thanks!

And Kem thanks for showing me how to properly do the prepare statement with the question mark. :slight_smile:

For those who use PostgreSQL, you should use “$” instead of “?”

$+digit for Postgres, e.g., “… WHERE field1=$1 AND field2=$2”.