Command parameters and SQL syntax errors

It's a shame that when you do certain things properly you lose out on usability.

Had I been doing it the wrong way by concatenating a SQL String, I could examine the final database command before it gets sent to the data-driver to check for syntax errors.

....but when using ADODB Parameters you can't review the final command sent to the server.

This code throws an error for some reason, but why?

(old-fashioned VB6 using MDAC2.8 btw)

Public Sub UserAdd(user As User)
    
    Dim sql As String
    sql = "INSERT INTO Users " & _
"([Name], Password, Address1, Address2, PostCode, DOB, [Email], Telephone, MobilePhone, AccountType, [DateCreated]) VALUES " & _
"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"
    
    m_conn.Open
    
    Dim cmd As New ADODB.Command
    Set cmd.ActiveConnection = m_conn
    cmd.CommandText = sql
    
    Dim nameP As ADODB.Parameter, passP As ADODB.Parameter, adr1P As ADODB.Parameter, adr2P As ADODB.Parameter
    Dim postP As ADODB.Parameter, dobiP As ADODB.Parameter, emilP As ADODB.Parameter, phonP As ADODB.Parameter
    Dim mobiP As ADODB.Parameter, typeP As ADODB.Parameter, cretP As ADODB.Parameter
    
    Set nameP = cmd.CreateParameter(, adVarWChar, adParamInput, 50, User.Name)
    Set passP = cmd.CreateParameter(, adVarWChar, adParamInput, 50, User.Password)
    Set adr1P = cmd.CreateParameter(, adVarWChar, adParamInput, 50, User.Address1)
    Set adr2P = cmd.CreateParameter(, adVarWChar, adParamInput, 50, User.Address2)
    Set postP = cmd.CreateParameter(, adVarWChar, adParamInput, 9, User.Password)
    Set dobiP = cmd.CreateParameter(, adDate, adParamInput, , User.DateOfBirth)
    Set emilP = cmd.CreateParameter(, adVarWChar, adParamInput, 50, User.EmailAddress)
    Set phonP = cmd.CreateParameter(, adVarWChar, adParamInput, 15, User.TelephoneNumber)
    Set mobiP = cmd.CreateParameter(, adVarWChar, adParamInput, 15, User.MobileTelephoneNumber)
    Set typeP = cmd.CreateParameter(, adUnsignedTinyInt, adParamInput, , CInt(User.AccountType))
    Set cretP = cmd.CreateParameter(, adDate, adParamInput, , User.AccountCreationDate)
    
    cmd.Parameters.Append nameP
    cmd.Parameters.Append passP
    cmd.Parameters.Append adr1P
    cmd.Parameters.Append adr2P
    cmd.Parameters.Append postP
    cmd.Parameters.Append dobiP
    cmd.Parameters.Append emilP
    cmd.Parameters.Append phonP
    cmd.Parameters.Append mobiP
    cmd.Parameters.Append typeP
    cmd.Parameters.Append cretP
    
    cmd.Execute
    
    m_conn.Close
    
    
End Sub

W3bbo wrote:



    Dim sql As String
    sql = "INSERT INTO Users " & _
"([Name], Password, Address1, Address2, PostCode, DOB, [Email], Telephone, MobilePhone, AccountType, [DateCreated]) VALUES " & _
"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"
    
    Set adr2P = cmd.CreateParameter(, adVarWChar, adParamInput, 50, User.Address2)
    Set postP = cmd.CreateParameter(, adVarWChar, adParamInput, 9, User.Password)
    Set dobiP = cmd.CreateParameter(, adDate, adParamInput, , User.DateOfBirth)

Try it like this instead (shortened for simplicity)...

Public Sub UserAdd(user As User)
    
    Dim sql As String
    sql = "INSERT INTO Users ([Name]) VALUES(@Name)"
    
    m_conn.Open
    
    Dim cmd As New ADODB.Command
    Set cmd.ActiveConnection = m_conn
    cmd.CommandText = sql
    
    Dim nameP As ADODB.Parameter 
   
    Set nameP = cmd.CreateParameter("@Name", adVarWChar, adParamInput, 50, User.Name)
    
    cmd.Parameters.Append nameP
    
    cmd.Execute
    
    m_conn.Close
    
    
End Sub