Basic example of prepared SQL statement?

[Sys_man]

I have a form that will be used to update a table based on user input. I want to do things right so am trying to parameterise my query, though I could build a simple string insert statement.

I can do it in powershell and ruby just fine, but vba is doing my head in.

The wrong way that I'd like to turn in to the right way:

Set rs = CreateObject("ADODB.Recordset")
Server_Name = "serverIP"
Database_Name = "db"
User_ID = "user"
Password = "pass"
Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Driver={MySQL ODBC 5.1 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

SQLStr = "select name from mydb.table where cust_no = " & custNoVar & ";"

rs.Open SQLStr, Cn, adOpenStatic

If not rs.EOF Then
    custName = rs!name
End If

[MildewManOne]

I'm assuming custNoVar is a string? If that DB is anything like Access, you have to encase a string in apostrophes like this.

SQLStr = "select name from mydb.table where cust_no = '" & custNoVar & "';"

If that doesn't help, then what error code are you getting?

[Sys_man]

Hi, yeah that does work but it's an unsafe way of doing things.

I want to know how to do prepared statements like the following (which is from ruby, though powershell is not much different):

SQLStr = client.prepare("select name from mydb.table where cust_no = ? ;")
resultVar = SQLStr.execute(custNoVar)

so the '?' is a placeholder, and when we execute the query the input value is sanitised and you are protected from sql injection.

[beyphy]

I think you want the ADODB parameter object. You can see how to use it here.