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 couldn't 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

Edited February 24, 2006 by flynn