Tech Off Thread

4 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Command parameters and SQL syntax errors

Back to Forum: Tech Off
  • User profile image
    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?

  • User profile image
    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.

  • User profile image
    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
  • User profile image
    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

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.