Comparing data entry to stored database information

[ABOU]

If i wanted to compare an ID entered in a textbox on a form to the already stored IDs in the CID column of the database to prevent data duplication how would i do this?

 

[jmcilhinney]

Dim cmd As New SqlCommand("SELECT COUNT(*) FROM MyTable WHERE ID = @ID", con)

cmd.Parameters.AddWithValue("@ID", id)
con.Open()

If CInt(cmd.ExecuteScalar()) = 0 Then
    'The ID does not exist.
Else
    'The ID already exists.
End If

con.Close()

 

[ABOU]

can i use this code in a vb.net form? I am getting errors with the sqlCommand type saying its not defined...

 

[MattP]

You've added Imports System.Data.SqlClient?

 

[ABOU]

It is now, still giving me errors.

name id is not declared
name con is not declared

sorry guys never dealt with sql before :S

 

[jmcilhinney]

So declare them. If I post example code then it's exactly that: an EXAMPLE. It's up to you to fit it into your project. If you're going to execute a query against SQL Server then you need an SqlConnection. That's what the 'con' variable refers to. The 'id' variable should fairly self-explanatory.

If you know nothing about ADO.NET then I suggest that you do some reading on ADO.NET. There are beginner tutorials all over the place.

 

[cjard]

you know.. I think you'd be better off NOT trying to see if a value exists before you add it.. just set the DB to make that column unique and go right ahead and try and add it. simple reason being, in the time between your check and your insert, some other client might have also checked then inserted that value..

just try and insert it and handle the error if it fails, or even better, learn how to write a concurrent system using sequences so that new ids are generated by the database..