Tech Off Thread

4 posts

Command parameters and SQL syntax errors

Back to Forum: Tech Off
  • W3bbo

    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
    Any ideas?

  • LostIn​Tangent

    The code looks fine from what I can tell. Have you tried using SQL Profiler to see the exact query being sent? That might shed some light on the matter.

  • cheong

    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)

    My guess is PostCode is numeric field, therefore cannot handle string password...

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • nlondon

    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

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.