Looking for existing data...
-
Hello, I am trying to look in a table to see if the item already exisits before I add another one. I have a simple table with 2 fields. The primary key is one and it is an auto number, the other is the description that I am trying to check for. The name of my table is OrdersListBoxInfo and the table name I am trying to compare with is Order. Once I get to the line "Count = cmd.ExecuteScalar" I am getting error "No value given for one or more required parameters." Here's the code I am running. Can anyone tell me what I am missing/doing wrong? Dim OrderName As String OrderName = txtOrderName.Text 'Check to see if this Order already exisits Dim Count As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Costco\My Documents\Visual Studio Projects\AnesXL.mdb") Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count( OrderName ) as ExistingRecords From [OrdersListBoxInfo] where [Order]=?", _ Connection) cmd.Parameters.AddWithValue("@Order", OrderName) Connection.Open() Count = cmd.ExecuteScalar End Using If Count > 0 Then MessageBox.Show("Number of rows found: " & Count, "Add Date", MessageBoxButtons.OK) Return End If
-
Hello, I am trying to look in a table to see if the item already exisits before I add another one. I have a simple table with 2 fields. The primary key is one and it is an auto number, the other is the description that I am trying to check for. The name of my table is OrdersListBoxInfo and the table name I am trying to compare with is Order. Once I get to the line "Count = cmd.ExecuteScalar" I am getting error "No value given for one or more required parameters." Here's the code I am running. Can anyone tell me what I am missing/doing wrong? Dim OrderName As String OrderName = txtOrderName.Text 'Check to see if this Order already exisits Dim Count As Integer = 0 Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Costco\My Documents\Visual Studio Projects\AnesXL.mdb") Dim cmd As OleDbCommand = New OleDbCommand _ ("Select Count( OrderName ) as ExistingRecords From [OrdersListBoxInfo] where [Order]=?", _ Connection) cmd.Parameters.AddWithValue("@Order", OrderName) Connection.Open() Count = cmd.ExecuteScalar End Using If Count > 0 Then MessageBox.Show("Number of rows found: " & Count, "Add Date", MessageBoxButtons.OK) Return End If
You said in your description you have two tables. But, this operation you're describing is only working on one table. Which is it? Shouldn't the WHERE clause lok like this:
SELECT COUNT(OrderName) As ExistingRecords FROM OrdersListBoxInfo WHERE OrderName=?
I also don't like using
AddWithValue. It leaves too much up to interpretation by the OleDbParameter class. I'd do it like this instead:cmd.Parameters.Add("@OrderName", OleDbType.VarChar, 50).Value = OrderName
You might also want to validate what's in OrderName before you assign the value to the parameter.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007