Need help with command parameters

[flynn]

I can insert records into an Access database with this statement:

cmdAdd.CommandText = "INSERT INTO tblTest (Parm1, Parm2, Parm3, Parm4, Parm5, Parm6, Parm7, Parm8) VALUES" & _
"(" & Parm1 & "," & _
        Parm2 & "," & _
        Parm3 & "," & _
        Parm4 & "," & _
        Parm5 & "," & _
        Parm6 & "," & _
        Parm7 & "," & _
        Parm8 & ")"

        cmdAdd.ExecuteNonQuery()

but if I try to parameterize the statement like this:

 cmdAdd.CommandText = "INSERT INTO tblTest (" & _
                    "Parm1, Parm2, Parm3, Parm4, Parm5, Parm6, Parm7, Parm8) VALUES (" & _
                    "@Parm1, @Parm2, @Parm3, @Parm4, @Parm5, @Parm6, @Parm7, @Parm8)"

                cmdAdd.Parameters.Add("@Parm1", OdbcType.VarChar, 8).Value = lvItem.Text
                cmdAdd.Parameters.Add("@Parm2", OdbcType.VarChar, 50).Value = lvItem.SubItems(1).Text
                cmdAdd.Parameters.Add("@Parm3", OdbcType.Date, 10).Value = CDate(lvItem.SubItems(2).Text)
                cmdAdd.Parameters.Add("@Parm4", OdbcType.VarChar, 255).Value = lvitem.SubItems(3).Text
                cmdAdd.Parameters.Add("@Parm5", OdbcType.VarChar, 255).Value = lvitem.SubItems(4).Text
                cmdAdd.Parameters.Add("@Parm6", OdbcType.VarChar, Int32.MaxValue).Value = lvitem.SubItems(5).Text
                cmdAdd.Parameters.Add("@Parm7", OdbcType.VarChar, 255).Value = lvitem.SubItems(6).Text
                cmdAdd.Parameters.Add("@Parm8", OdbcType.VarChar, 50).Value = lvitem.SubItems(7).Text

                cmdAdd.ExecuteNonQuery()

I get this error:

System.Data.Odbc.OdbcException was caught
  ErrorCode=-2146232009
  Message="ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 8."
  Source="odbcjt32.dll"
  StackTrace:
       at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
       at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
       at DataParser.Main.btnSaveData_Click(Object sender, EventArgs e) in C:\Visual Studio 2005\Projects\Data\Data1\Main.vb:line 574

Any suggestions?

tia,
flynn

 

[Joe Mamma]

First I would switch to the Jet 4.0 OleDb Provider


but this should work for both:

cmdAdd.CommandText = "INSERT INTO tblTest (" & _
"Parm1, Parm2, Parm3, Parm4, Parm5, Parm6, Parm7, Parm8) VALUES (" & _
"?, ?, ?, ?, ?, ?, ?, ?)"
cmdAdd.Parameters.AddWithValue("Parm1", lvItem.Text)
cmdAdd.Parameters.AddWithValue("Parm2", lvItem.SubItems(1).Text)
cmdAdd.Parameters.AddWithValue("Parm3", CDate(lvItem.SubItems(2).Text))
cmdAdd.Parameters.AddWithValue("Parm4", lvitem.SubItems(3).Text)
cmdAdd.Parameters.AddWithValue("Parm5", lvitem.SubItems(4).Text)
cmdAdd.Parameters.AddWithValue("Parm6", lvitem.SubItems(5).Text)
cmdAdd.Parameters.AddWithValue("Parm7", lvitem.SubItems(6).Text)
cmdAdd.Parameters.AddWithValue("Parm8", lvitem.SubItems(7).Text)
cmdAdd.ExecuteNonQuery()

 

[flynn]

First I would switch to the Jet 4.0 OleDb Provider


but this should work for both:

cmdAdd.CommandText = "INSERT INTO tblTest (" & _
"Parm1, Parm2, Parm3, Parm4, Parm5, Parm6, Parm7, Parm8) VALUES (" & _
"?, ?, ?, ?, ?, ?, ?, ?)"
cmdAdd.Parameters.AddWithValue("Parm1", lvItem.Text)
cmdAdd.Parameters.AddWithValue("Parm2", lvItem.SubItems(1).Text)
cmdAdd.Parameters.AddWithValue("Parm3", CDate(lvItem.SubItems(2).Text))
cmdAdd.Parameters.AddWithValue("Parm4", lvitem.SubItems(3).Text)
cmdAdd.Parameters.AddWithValue("Parm5", lvitem.SubItems(4).Text)
cmdAdd.Parameters.AddWithValue("Parm6", lvitem.SubItems(5).Text)
cmdAdd.Parameters.AddWithValue("Parm7", lvitem.SubItems(6).Text)
cmdAdd.Parameters.AddWithValue("Parm8", lvitem.SubItems(7).Text)
cmdAdd.ExecuteNonQuery()

Joe,

I used the code above but got this:

System.Data.Odbc.OdbcException was caught
ErrorCode=-2146232009
Message="ERROR [HY104] [Microsoft][ODBC Microsoft Access Driver]Invalid precision value "
Source="odbcjt32.dll"
StackTrace:
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcParameter.Bind(OdbcStatementHandle hstmt, OdbcCommand command, Int16 ordinal, CNativeBuffer parameterBuffer, Boolean allowReentrance)
at System.Data.Odbc.OdbcParameterCollection.Bind(OdbcCommand command, CMDWrapper cmdWrapper, CNativeBuffer parameterBuffer)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at DataParser.Main.btnSaveData_Click(Object sender, EventArgs e) in C:\Documents and Settings\jeff wohltman\My Documents\Visual Studio 2005\Projects\DataParser5\DataParser\Main.vb:line 560

I used this Microsoft page to determine if I have the current driver, which I do:
http://support.microsoft.com/default.aspx?kbid=239114#XSLTH3120121123120121120120

Are you saying that I am using the ODBC driver where I should be using the OLE driver? If so, do you have a link to the current OLE driver? I couldnt find it by wading through all the muck returned by Google.

EDIT:
I found a Microsoft page that shows the files that are in the latest update. I have the OLEDB driver (I think): msjetoledb40.dll (351k). How do I setup the DSN to use this driver? Or can I not use a DSN with the OLEDB driver?

tia,
flynn