Problems using a SQL INSERT command with ADODB 2

[tony00]

I usually program databases using ADODB.Connections, ADODB.Recordsets and Command objects.

I create the connection and command objects this way (I'm connecting to an Access database):

Set conect = New ADODB.Connection
conect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\Passwords.mdb"
conect.Open
Set com = New Command
com.ActiveConnection = conect

In another part of my program, I use the command object to insert registers this way:

com.CommandText = "INSERT INTO Usuarios (Num,Usuario,Password) VALUES (" + txtNo + ",'" + txtUsuario + "','" + txtPassword + "')"
com.Execute

(the txtNo, txtUsuario and txtPassword are Textbox controls on the form).

In most of my applications it works, but in other apps I get an error, saying that there is a syntax error in the SQL INSERT instruction, even if the SQL command is correct (I try the same command in Access and it works).

What is wrong?

Thanks

 

[fredericofonseca]

It is possible that your users are entering a "'" (single quote) on the text.

For you to be sure you need to add the SQL used to an error raised by your application.

e.g.

on error goto error_handler
sSQL = "INSERT INTO Usuarios (Num,Usuario,Password) VALUES (" + txtNo + ",'" + txtUsuario + "','" + txtPassword + "')"
com.CommandText = sSQL
com.Execute

exit ...
error_handler
msgbox "error in SQL = " & sSQL
exit ...


I also noticed that you are using a command object. This means that you are better off using parameters to pass the variable values to the SQL.

Search these forums/faq's for command parameters and also for "sql injection" and you will see some good reasons for changing.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[johnwm]

Also note that the concatenation operator in VB is ampersand, not plus.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[zemp]

Single quotes, SQL injection - check out faq709-1526.

zemp

 

[tony00]

Thanks for your help. I used parameters, but sometimes it works and on other applications it dosen´t. Let me keep on looking and when I find an answer. I'll let you know.

 

[fredericofonseca]

hum... Parameters should always work unless you have bad data, or you are trying to insert/update and you get a constraint error (e.g. duplicate key).

Maybe you wish to place an updated version of your code with the parameters and state the errors you get, and we will try and see if further help is needed.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[tony00]

The error message I get is Runtime error -2147217900 (80040e14) "Syntax error in the INSERT INTO instruction".

The code to declare, create and open the connection and command are the same. This is my code that does the insert with parameters...

Dim paraNo As ADODB.Parameter
Dim paraNombre As ADODB.Parameter
Dim paraPass As ADODB.Parameter
. . .
com.CommandText = "INSERT INTO Usuarios (Num,Usuario,Password) VALUES (?,?,?)"
Set paraNo = com.CreateParameter("Num", adInteger, adParamInput, 4)
Set paraNombre = com.CreateParameter("Usuario", adChar, adParamInput, 50)
Set paraPass = com.CreateParameter("Password", adChar, adParamInput, 50)
com.Parameters.Append paraNo
com.Parameters.Append paraNombre
com.Parameters.Append paraPass
paraNo.Value = txtNo
paraNombre.Value = txtUsuario
paraPass.Value = txtPassword
com.Execute , , adExecuteNoRecords

Thanks for your help!

Tony =

 

[Golom]

The com.Execute will attempt to run

INSERT INTO Usuarios (Num,Usuario,Password) VALUES (?,?,?)

but it won't substitute your parameters to replace the question marks. Try something like

com.CommandText = "INSERT INTO Usuarios (Num,Usuario,Password) " & _ 
"VALUES ( " & txtNo & ", '" & txtUsuario & "', '" & txtPassWord & "')"

com.Execute , , adExecuteNoRecords

 

[fredericofonseca]

excuse me???

The code below is a extract of production code.

Set adoComm = New ADODB.Command
adoComm.CommandType = adCmdText
sSQL = ""
sSQL = sSQL & " INSERT INTO [dbo].[mytbl]"
sSQL = sSQL & " (ASTTYPE, ASSETMAK)"
sSQL = sSQL & " VALUES (?, ?)"
adoComm.Parameters.Append adoComm.CreateParameter("p1", adChar, adParamInput, 1)
adoComm.Parameters.Append adoComm.CreateParameter("p2", adChar, adParamInput, 15)


adoComm.CommandText = sSQL
adoComm.ActiveConnection = adoConn
adoConn.BeginTrans
i = 1
While Not wReal_daily.Cells(i, 1).Value = ""
  With adoComm.Parameters
    .Item("p1").Value = wReal_daily.Cells(i, 1).Text
    .Item("p2").Value = wReal_daily.Cells(i, 2).Text
  End With
  adoComm.Execute , , adExecuteNoRecords
  i = i + 1
Wend
adoConn.CommitTrans

As you can see from the above the "?" get replaced by the value assigned to the respective parameter

When you use parameters the question marks get replaced by the corresponding parameter (positional on this case), and the value is assigned by
paraNombre.Value = txtUsuario

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Golom]

fredericofonseca

Sorry about the generalization. In your SQL server code that does happen but note at the beginning of the poster's original message

I'm connecting to an Access database

Access would require explicit references to the parameter names in order to make the substitution. The "?" place-holder is not supported in Access.

 

[fredericofonseca]

Well... It works with Access 2003.

Private Sub Form_Load()
Dim sConn As String
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\processADO.mdb;User Id=admin;Password="
Set adoConn = New ADODB.Connection
adoConn.Open sConn
End Sub


Private Sub save_Click()
On Error GoTo error_handler
Dim i As Long
Dim bCommited As Boolean
    adoConn.BeginTrans
    Set adoComm = New ADODB.Command
    adoComm.ActiveConnection = adoConn
    adoComm.CommandType = adCmdText
    sSql = "insert into processes ("
    sSql = sSql + "process_id"
    sSql = sSql + ", process_seq"
    sSql = sSql + ", type"
    sSql = sSql + ", description"
    sSql = sSql + ", contents "
    sSql = sSql & ", logintext"
    sSql = sSql + ", loginid"
    sSql = sSql + ", pwdtext"
    sSql = sSql + ", pwd"
    sSql = sSql + ") values ("
    sSql = sSql + "?"
    sSql = sSql + ",?"
    sSql = sSql + ",?"
    sSql = sSql + ",?"
    sSql = sSql + ",?"
    sSql = sSql + ",?"
    sSql = sSql + ",?"
    sSql = sSql + ",?"
    sSql = sSql + ",?"
    sSql = sSql + ")"
    adoComm.CommandText = sSql
    
    adoComm.Parameters.Append adoComm.CreateParameter("p1", adChar, adParamInput, 20)
    adoComm.Parameters.Append adoComm.CreateParameter("p2", adInteger, adParamInput, 4)
    adoComm.Parameters.Append adoComm.CreateParameter("p3", adInteger, adParamInput, 4)
    adoComm.Parameters.Append adoComm.CreateParameter("p4", adChar, adParamInput, 50)
    adoComm.Parameters.Append adoComm.CreateParameter("p5", adVarWChar, adParamInput, 20000)
    adoComm.Parameters.Append adoComm.CreateParameter("p6", adChar, adParamInput, 50)
    adoComm.Parameters.Append adoComm.CreateParameter("p7", adChar, adParamInput, 50)
    adoComm.Parameters.Append adoComm.CreateParameter("p8", adChar, adParamInput, 50)
    adoComm.Parameters.Append adoComm.CreateParameter("p9", adChar, adParamInput, 50)
    Dim s As String
    s = sSql + sSql + sSql + sSql + sSql + sSql + sSql + sSql + sSql + sSql + sSql + sSql + sSql + sSql + sSql + sSql
    s = s + s
    
    adoComm.Parameters("P1").Value = GetString(proc_id.Text)
    For lRow = 0 To flex1.Rows - 1
      flex1.Row = lRow
      flex1.Col = 0
      If flex1.Text <> "" Then
        adoComm.Parameters("P2").Value = lRow
        adoComm.Parameters("P3").Value = list1.ItemData(lRow)
        flex1.Col = flex1.Col + 1
        adoComm.Parameters("P4").Value = GetString(flex1.Text)
        flex1.Col = flex1.Col + 1
        flex1.Text = s
        adoComm.Parameters("P5").AppendChunk (Mid(GetString(flex1.Text), 1, 20000))
        flex1.Col = flex1.Col + 1
        adoComm.Parameters("P6").Value = GetString(flex1.Text)
        flex1.Col = flex1.Col + 1
        adoComm.Parameters("P7").Value = GetString(flex1.Text)
        flex1.Col = flex1.Col + 1
        adoComm.Parameters("P8").Value = GetString(flex1.Text)
        flex1.Col = flex1.Col + 1
        adoComm.Parameters("P9").Value = GetString(flex1.Text)
        adoComm.Execute , , adExecuteNoRecords
      End If
    Next lRow
    adoConn.CommitTrans
    bCommited = True
    Exit Sub
error_handler:
    If bCommited Then ' error occurred after commiting changes
         Exit Sub
    End If
    For i = 0 To adoConn.Errors.Count - 1
      ADOErrors = ADOErrors & adoConn.Errors(i).SQLState & "=" & adoConn.Errors(i).Description & vbCrLf & Err.Description
    Next i
    i = MsgBox("Doing a ROLLBACK. All information was reverted." & vbCrLf & "Last SQL being executed: " & sSql & vbCrLf & "Ado error = " & ADOErrors)
    adoConn.RollbackTrans

End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

try the following code.

Just before your code
com.CommandText = "INSERT INTO Usuarios (Num,Usuario,Password) VALUES (?,?,?)"
add
call tst_insert (txtNo, txtUsuario, txtPassword)

This code assumes that
A- conect is defined globaly
B- txtNo, txtUsuario, txtPassword are fields that you populate with the value of your text boxes. I hope that on your code they are not the name of controls. If they are then change the above (and indeed your code) to be

call tst_insert (txtNo.value, txtUsuario.value, txtPassword.value)


Also note that I have not tested this code.

Sub tst_insert(txtNo As Integer, txtUsuario As String, txtPassword As String)
On Error GoTo error_handler
Dim com As ADODB.Command
Dim sSql As String
Dim ADOErrors As String
Dim i As Long

Set com = New ADODB.Command
com.ActiveConnection = conect
com.CommandType = adCmdText
sSql = "INSERT INTO Usuarios (Num,Usuario,Password) VALUES (?,?,?)"

com.CommandText = sSql
com.Parameters.Append com.CreateParameter("Num", adInteger, adParamInput, 4)
com.Parameters.Append com.CreateParameter("Usuario", adChar, adParamInput, 50)
com.Parameters.Append com.CreateParameter("Password", adChar, adParamInput, 50)

com.Parameters("Num").Value = txtNo
com.Parameters("Usuario").Value = txtUsuario
com.Parameters("Password").Value = txtPassword

com.Execute , , adExecuteNoRecords

    Exit Sub
error_handler:
    For i = 0 To conect.Errors.Count - 1
      ADOErrors = ADOErrors & conect.Errors(i).SQLState & "=" & conect.Errors(i).Description & vbCrLf & Err.Description
    Next i
    i = MsgBox("Last SQL being executed: " & sSql & vbCrLf & "Ado error = " & ADOErrors)


End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com