[2005] Record Exist Problem?

[crampz]

The Following code i am using at the moment is to ADD (only) to a database.

 
        Dim connection As New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ..\db1.mdb")
        Dim command As New OleDbCommand("INSERT INTO Table1 (Barcode, Box, Description, Whos) VALUES (@Barcode, @Box, @Description,@Whos)", connection)
        command.Parameters.AddWithValue("@Barcode", txtBarcode.Text)
        command.Parameters.AddWithValue("@Box", txtBox.Text)
        command.Parameters.AddWithValue("@Description", rttDetail.Text)
        command.Parameters.AddWithValue("@Whos", txtWho.Text)
        connection.Open()
        command.ExecuteNonQuery()
        connection.Close()

But what i need before it adds it to the database is too check the Barcode column to see if what is typed in the txtbarcode.text textbox matches anything in the barcode column in the DB. if it does a messagebox appears to tell them, if not carry on.

any help is helpful

thanks

[jmcilhinney]

So you need to query the database to see if there are any existing records with matching values. Follow the Data Access link in my signature to see how to use ExecuteScalar to get a single value from a database. You query would be something like:

SELECT COUNT(*) FROM SomeTable WHERE SomeColumn = @SomeColumn

If the result is non-zero then the value already exists in the database.

[talkro]

dim BarCOdeQuery as string = "SELECT * FROM Table WHERE Barcode = @BarCode"
dim connection as SqlConnection = new SalCOnnection(connectionString)
dim command as new SqlCOmmand(BarCodeQuery, connection)
 
try
   connection.open
   dim result as object = command.executeScalar()
   if not isNothing(result) then throw new Exception("Code bar already exists")
   
   'Save as you did before
Catch ex as exception
   messagebox.show(ex.tostring)
Finally
   connection.close
end try

[crampz]

Thanks For Your Help. I used this code to do it.

        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ..\db1.mdb"
        Dim command As New OleDbCommand("SELECT COUNT(Barcode) FROM Table1 WHERE Barcode = '" & txtBarcode.Text & "'", con)
        con.Open()
        Dim total As Double = CDbl(command.ExecuteScalar())
        con.Close()
        If total = "1" Then
            MessageBox.Show("Record Already Exists - [Barcode]", "Existing Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtBarcode.Text = ""
        End If

This works perfectly, but only for the first column - Barcode.

when i used this code to use with the box column

        
Dim con1 As New OleDb.OleDbConnection
        Dim ds1 As New DataSet
        con1.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ..\db1.mdb"
        Dim command1 As New OleDbCommand("SELECT COUNT(Box) FROM Table1 WHERE Box = '" & txtBox.Text & "'", con1)
        con1.Open()
        Dim total1 As Double = CDbl(command1.ExecuteScalar())
        con1.Close()
        If total1 = "1" Then
            MessageBox.Show("Record Already Exists - [Box Number]", "Existing Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtBox.Text = ""
        End If

it throws up an error:

Data type mismatch in criteria expression.

for this line of code:

Dim total1 As Double = CDbl(command1.ExecuteScalar())

Is this because the .ExecueScalar command is only for the first column. and how do i resolve this.

thank you

[jmcilhinney]

The problem is that the value you search for has to be the same type as the column you search in. For instance, you can't search a numeric column for "Hello World". What's the type of the column you're searching in? Is the parameter value you're passing compatible? Obviously the answer to the second question is NO.

[crampz]

The type of column is a "Number" column (1,2,3 etc.)

[jmcilhinney]

So... pass a number to search for and not text. Single quotes around a value mean it's text. That said, you should be using parameters anyway. Follow the Data Access link in my signature to see how.