How does a parameter get a default value?

[mechwarrior3]

How does a parameter get a default value? (RESOLVED)

Hello everyone,

I have attached an image of the exception I continue to smash my head into even as I make this post. This exception occurs in this little block of code:

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] verify [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbConnection(MYACCESSCONN)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessDataSet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.DataSet
AccessAdapter.SelectCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("SELECT * FROM tbl_HCI_Lot_Inspection_Data WHERE strLotNumber = ? AND strMixID = ?")
AccessAdapter.SelectCommand.Connection = AccessConn
AccessAdapter.SelectCommand.Parameters.Add("@MfgNum", OleDb.OleDbType.VarChar, 50, "strLotNumber")
AccessAdapter.SelectCommand.Parameters.Add("@MixNum", OleDb.OleDbType.VarChar, 50, "strMixID")
[/SIZE][SIZE=2][COLOR=#008000]' The .Fill routine returns the number of rows filled with data so in our case, as long as there is one row
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]' filled, we have verified that the record exists within the database.
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]verify = AccessAdapter.Fill(AccessDataSet, "tbl_HCI_Lot_Inspection_Data")
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] sqlEx [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDb.OleDbException
MessageBox.Show(sqlEx.ToString)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]' verify = AccessAdapter.Fill(AccessDataSet, "tbl_HCI_Inspection_Lot_Data")
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] (verify >= 1) [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]blnThere = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]blnThere = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]

What I want to do is see if there are any records within my database that match the two parameters at the same time within the SELECT statement. Essentially, I should only have one record or no records that correctly match the two parameters within the SELECT statement. I read that the .Fill method returns an integer value signifying the amount of rows filled within the data set so that is why I am using the integer, verify, to see just how many rows were filled. Why do I get the error about the parameter not having a default value?

I use data adapters and the .Fill command in other parts of my code and have not encountered this problem. I am having trouble finding anything within my code that could explain this problem.

Any help is greatly appreciated. Thanks, everyone.

 

[TechGnome]

Made some assumptions and changes to the code....

AccessAdapter.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM tbl_HCI_Lot_Inspection_Data WHERE strLotNumber = '?' AND strMixID = '?'")
AccessAdapter.SelectCommand.Connection = AccessConn
AccessAdapter.SelectCommand.Parameters.Add("@MfgNum", OleDb.OleDbType.VarChar, 50, strLotNumber)
AccessAdapter.SelectCommand.Parameters.Add("@MixNum", OleDb.OleDbType.VarChar, 50, strMixID)

Assuming strLotNumber and strMixID are text fields in the table.... the Select was changed.

Assuming that strLotNumber and strMixID are variables that contain the values being looked for, the Parameters.Add lines were changed.

-tg

 

[mechwarrior3]

Hmmm. I modified the SELECT command as you suggested but not the .Add lines because strLotNumber and strMixID are the names of the columns within the table. The .Add function says that those strings need to be the names of the source columns from the table.

From the change made to the SELECT statement, the .Fill command executes properly, however, a zero is returned for verify. In other words, I am not filling any rows even though I am entering values that I know to exist in the database. Here is an INSERT command I also have in my code and this command works correctly:

[SIZE=2]strCommand = "INSERT INTO tbl_HCI_Lot_Inspection_Data (strMixID, strLotNumber, strArcTube," _
& " strDebindDate, strPrefireDate, strFinalfireDate, strInspectionDate, ysnReclaimed, nLotGross, nLotNet," _
& " strIntendedCustomer, nVIGross, intWSDefectives, intCEGGDefectives, intOPDefectives, intBSDefectives," _
& " intRSDefectives, intVoidDefectives, intTearOutDefectives, intChipDefectives, intScratchDefectives," _
& " intCrackDefectives, intFlashDefectives, intGrayDefectives, intJointDefectives, intOther1Defective," _
& " intOther2Defective, intOther3Defective, intVisualDefectsNET, nPinTestGross, intCoaxPinDefectives," _
& " int5mmPinDefectives, intCapIDPinDefectives, intNoInsPinDefectives, intFFUHatDefectives," _
& " intFFUPinDefectives, intPinTestNET, nWSGross, nBSGross, nRSGross, nVoidGross, nTearOutGross," _
& " nCrackGross, nODChipGross, nIDChipGross, nScratchGross, nCEGGGross, nOPGross, nFlashGross," _
& " nGrayGross, nJointGross, nOther1Gross, nOther2Gross, nOther3Gross, intTransferred, nCoaxPinGross," _
& " n5mmPinGross, nCapIDGross, nNoInsPinGross, nFFUHatGross, nFFUPinGross, nDimsLongGross," _
& " nDimsShortGross, nDimsBigODGross, nDimsSmallODGross, nDimsBigCapODGross, nDimsSmallCapODGross," _
& " strPartNum, nRegrind, intODChipDefectives, intIDChipDefectives, strEmployeeID)" _
& " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," _
& "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
 
[SIZE=2]AccessAdapter.InsertCommand.Parameters.Add("@MixNum", OleDb.OleDbType.VarChar, 50, "strMixID")
AccessAdapter.InsertCommand.Parameters.Add("@MfgNum", OleDb.OleDbType.VarChar, 50, "strLotNumber")
AccessAdapter.InsertCommand.Parameters.Add("@ArcNum", OleDb.OleDbType.VarChar, 50, "strArcTube")
etc. ...
[SIZE=2]AccessAdapter.Fill(AccessDataSet, "tbl_HCI_Lot_Inspection_Data")
[/SIZE][/SIZE][/SIZE]

How come this command works and the .Fill command does fill in rows whereas this other, new SELECT command does not?

EDIT: I have tried my SELECT command with no WHERE clause and my variable "verify" returns a value of 7 which is the total number of rows in my table. How come verify still equals 0 after running the SELECT statement with the WHERE clause? Am I not adding parameters correctly?

 

[mechwarrior3]

Got It! :d :d

Fixed it! This is how the code had to be implemented:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strLotNumber [/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].txtMfgLotNum.Text
[/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 strLotNumber = ? AND strMixID = ?")
AccessAdapter.SelectCommand.Connection = AccessConn
AccessAdapter.SelectCommand.Parameters.Add("@MfgNum", strLotNumber)
AccessAdapter.SelectCommand.Parameters.Add("@MixNum", strMixID)

Thank you for your help, TG. That was pretty silly of me to try to use the columns as the source for the search. Hehe. The .Add method has this version that only needs the parameter name and a value as type object. Now everything works just fine. Thanks again.
[/SIZE]