noc_coder noc_coder - 2 months ago 10
MySQL Question

Parameters not adding to ADO string for INSERT statement VBA

I am trying to learn how to avoid SQL injection and am using VBA connecting to a mysql DB via ADO in VB.

The problem I am having is that for the line

Set rs = cmd.Execute


I get the following error that I have not been able to figure in two days: "No value given for one or more required parameters".

And surely, when printing the param string, it returns this: (I have noticed the difference in params.. not sure why it occurs)

INSERT INTO prm1=? VALUES prm2=?, prm3=?, prm4=?, prm5=?, prm6=?, prm7=?, prm8=?, prm9=?, prm10=?, prm11=?, prm12=?, prm13=?;


This is the code that I am using for clarification:

Function addToServer(file As String, server As Integer, lastRow As Integer)

Sheets("usage").Select
Dim str As String
Dim i As Integer

Dim conn As ADODB.Connection
Set conn = DBConnection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim cmd As ADODB.Command
Dim prm2 As ADODB.Parameter
Dim prm3 As ADODB.Parameter
Dim prm4 As ADODB.Parameter
Dim prm5 As ADODB.Parameter
Dim prm6 As ADODB.Parameter
Dim prm7 As ADODB.Parameter
Dim prm8 As ADODB.Parameter
Dim prm10 As ADODB.Parameter
Dim prm11 As ADODB.Parameter
Dim prm12 As ADODB.Parameter
Dim prm13 As ADODB.Parameter
Dim prm14 As ADODB.Parameter
Dim prm15 As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn

With cmd
If server <> 0 Then
.CommandText = "INSERT INTO NLVMerlinResults (Year, Month, Day, Lab, Station, IP, thisWeek, Week1, Week2, Week3, Week4, Week5, WeeksInMonth, SumOverWeeks) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
Else
.CommandText = "INSERT INTO STMerlinResults (Year, Month, Day, Lab, Station, IP, thisWeek, Week1, Week2, Week3, Week4, Week5, WeeksInMonth, SumOverWeeks) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ;"
End If

cmd.CommandType = adCmdText

Set prm2 = .CreateParameter(Name:="year", Type:=adInteger, size:=4)
.Parameters.Append prm2
Set prm3 = .CreateParameter(Name:="month", Type:=adSmallInt, size:=2)
.Parameters.Append prm3
Set prm4 = .CreateParameter(Name:="day", Type:=adSmallInt, size:=2)
.Parameters.Append prm4
Set prm5 = .CreateParameter(Name:="lab", Type:=adVarChar, size:=30)
.Parameters.Append prm5
Set prm6 = .CreateParameter(Name:="cell", Type:=adVarChar, size:=30)
.Parameters.Append prm6
Set prm7 = .CreateParameter(Name:="ip", Type:=adVarChar, size:=20)
.Parameters.Append prm7
Set prm8 = .CreateParameter(Name:="week", Type:=adSmallInt, size:=2)
.Parameters.Append prm8
Set prm9 = .CreateParameter(Name:="1", Type:=adVarChar, size:=10)
.Parameters.Append prm9
Set prm10 = .CreateParameter(Name:="2", Type:=adVarChar, size:=10)
.Parameters.Append prm10
Set prm11 = .CreateParameter(Name:="3", Type:=adVarChar, size:=10)
.Parameters.Append prm11
Set prm12 = .CreateParameter(Name:="4", Type:=adVarChar, size:=10)
.Parameters.Append prm12
Set prm13 = .CreateParameter(Name:="5", Type:=adVarChar, size:=10)
.Parameters.Append prm13
Set prm14 = .CreateParameter(Name:="weeksinmonth", Type:=adTinyInt, size:=1)
.Parameters.Append prm14
Set prm15 = .CreateParameter(Name:="total", Type:=adVarChar, size:=10)
.Parameters.Append prm15

Dim j As Integer
For i = 1 To lastRow
For j = 1 To 15
If (j = 1) Then
prm2.value = CLng(cells(i, j))
ElseIf (j = 2) Then
prm3.value = CInt(cells(i, j))
ElseIf (j = 3) Then
prm4.value = CInt(cells(i, j))
ElseIf (j = 4) Then
prm5.value = CStr(cells(i, j))
ElseIf (j = 5) Then
prm6.value = CStr(cells(i, j))
ElseIf (j = 6) Then
prm7.value = CStr(cells(i, j))
ElseIf (j = 7) Then
prm8.value = CInt(cells(i, j))
ElseIf (j = 8) Then
If IsEmpty((cells(i, j))) Then
prm9.value = vbNullString
Else
prm9.value = CStr(cells(i, j))
End If
ElseIf (j = 9) Then
If IsEmpty((cells(i, j))) Then
prm10.value = vbNullString
Else
prm10.value = CStr(cells(i, j))
End If
ElseIf (j = 10) Then
If IsEmpty((cells(i, j))) Then
prm11.value = vbNullString
Else
prm11.value = CStr(cells(i, j))
End If
ElseIf (j = 11) Then
If IsEmpty((cells(i, j))) Then
prm12.value = vbNullString
Else
prm12.value = CStr(cells(i, j))
End If
ElseIf (j = 12) Then
If IsEmpty((cells(i, j))) Then
prm13.value = vbNullString
Else
prm13.value = CStr(cells(i, j))
End If
ElseIf (j = 13) Then
prm14.value = CByte(cells(i, j))
ElseIf (j = 14) Then
prm15.value = CStr(cells(i, j))
End If
Next j
.Execute
Next i

End With
Set cmd = Nothing
End Function


I have two different approaches that I used shown above, but it seems neither of the two work. Can someone give general direction as to what I can do to help? I tried taking the params out from the .CreateParameter function (http://www.w3schools.com/asp/met_comm_createparameter.asp says they were optional), but that didnt work either.

Best,

Ashley

EDIT:: I have added edits above. I tried a test insert and it was successful.

INSERT INTO `NLVMerlinResults` (Year, Month, Day, Lab, Station, IP, thisWeek, Week1, Week2, Week3, Week4, Week5, WeeksInMonth, SumOverWeeks) VALUES ("1", "2", "3", "4", "5", 1, 5, "5", "5", "6", 6, "5", "4", "5")


Although its odd that it lets me enter integers and strings when the DB takes only varchar...

Answer

Remove the "qualifer=?" from the SQL statement. ODBC parameters are specified with a single ?, and have to be added in the same order as they appear in the statement:

INSERT INTO  `NLVMerlinResults` (Year, Month, Day, Lab, Station, IP, 
thisWeek, Week1, Week2, Week3, Week4, Week5, WeeksInMonth, SumOverWeeks) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

OP EDIT: Functional code provided in the question's statement.