r/vba•
Sys_man

Basic example of prepared SQL statement?

Unsolved

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

Posted May 27, 2020, 8:47 PM UTC

23
MildewManOne16•

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?

2
Sys_manOP•

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.

1
beyphy4•

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

1