mechwarrior3
Well-known member
- Joined
- Dec 19, 2005
- Messages
- 67
- Programming Experience
- Beginner
So I am working with a data adapter that has a select and update command. Now, when my code is set up like this:
[/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:
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]
VB.NET:
[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]
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:
VB.NET:
[/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]