Basic example of prepared SQL statement?
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
I'm assuming custNoVar is a string? If that DB is anything like Access, you have to encase a string in apostrophes like this.
If that doesn't help, then what error code are you getting?
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):
so the '?' is a placeholder, and when we execute the query the input value is sanitised and you are protected from sql injection.
I think you want the ADODB parameter object. You can see how to use it here.