Issue with parameters needing to have explicitly set types.

[mechwarrior3]

So I am working with a data adapter that has a select and update command. Now, when my code is set up like this:

[SIZE=2]AccessAdapter.SelectCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("SELECT * FROM tbl_HCI_Lot_Inspection_Data", AccessConn)[/SIZE]
[SIZE=2][SIZE=2]AccessAdapter.UpdateCommand = AccessCommand
AccessAdapter.UpdateCommand.Connection = AccessConn
AccessAdapter.UpdateCommand.Parameters.Add("@ArcNum", OleDb.OleDbType.VarChar, 50, "strArcTube")
...
[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]AccessAdapter.UpdateCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommandBuilder(AccessAdapter).GetUpdateCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] Ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDb.OleDbException
MessageBox.Show(Ex.ToString)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=black]

[/COLOR][/SIZE]

My code works just fine. However, this requires selecting an entire database and since this database has in excess of 40,000 records, it can be quite a slow process. I thought that I could speed things up by using a WHERE clause in my SELECT command. My new SELECT command looks like this:

[/COLOR][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2][COLOR=#000000] strLotNumber [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#000000] [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2][COLOR=#000000] = [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2][COLOR=#000000].txtMfgLotNum.Text[/COLOR]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strMixID [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtMixLotNum.Text
AccessAdapter.SelectCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("SELECT * FROM tbl_HCI_Lot_Inspection_Data WHERE strMixID = ? AND strLotNumber = ?", AccessConn)
AccessAdapter.SelectCommand.Parameters.Add("@MixNum", strMixID)
AccessAdapter.SelectCommand.Parameters.Add("@MfgNum", strLotNumber)
[COLOR=black]

Then, I get the error attached. The error occurs at the Try .UpdateCommand line.

What parameters need to have an explicitly set type? I've set the types for all of my parameters and the only difference between what works and what doesn't is the fact that I use variables in my SELECT command and a WHERE clause instead of just selecting the whole database. Why is this causing trouble and how can I fix it? Any help is greatly appreciated.
[/SIZE][/SIZE][/SIZE]

 

[vis781]

What the error is talking about is in the parameters, there is an argument you can pass in the set the type of data being retrieved... something like..


Cmd.parameters.add("@param1", OleDbType.VarWchar,field size,"Param1)

Also you havent specified a value for the WHERE arguments...

Cmd.parameters.add(as above).value = the value you want to query the database with

 

[mechwarrior3]

Resolved!

Ah. Well, there we go. Thank you very much, Vis. I didn't know there was a .Value after all of that parameter.add. Thank you very much for the help.