Check for value

[Arg81]

Its been a few months since I last did any programming, so I'm a little rusty and this may be a nice simple question!

I have a number of fields in my table, 2 of which are important. The first one is a Identity, so my SQL server sets that as needed. The 2nd field is a manually entered integer, we'll call this mixID.

Now the mixID can be set when a new record is entered, or by editing a record at a later date and assigning it.

However, no 2 mixID can be the same, so I need some kind of check in place to make sure the entered number doesn't already exist for any row in the DB.

I thought I could set some kind of "ID" on but not allow the DB to automatically assign it, obviously not.

Easy to achieve?

thanks!

 

[jmcilhinney]

You can set the appropriate property of that column in the database to require that that column be unique, which will ensure that no duplicate values are ever entered. That won't stop you trying though. It just means that the database will refuse and your code may throw an exception.

If you're populating a DataTable with the data from that table then such a unique constraint will be propagated to your DataTable when it's generated, or else you should be setting it when you create the column manually. Either way that will prevent any duplicate values being entered into the DataTable.

If you aren't getting the current data then you simply have to execute a query that returns a count of records with the value of interest in that column. If the query returns zero then you know the value is safe.

 

[cjard]

I agree with jmc's first suggestion; shove a unique index on that column in the DB and deal with any errors that arise as a result of atempting to insert a duplicate mixID

That said... if mixID must be unique, is it not a good PK candiate? Why do you have an autonumber, if you also have a mixid

 

[Arg81]

the "main" ID is set as autonumber, but at a later date that row may get transformed into something else (a mixID). These are set values from another program, so they have to be manually entered. Not every row will get a mixID.

I'll have a go at what you have suggested, thanks for the help.

 

[jmcilhinney]

Well, if some records will have null values then the unique constraint is out the window. You're just going to have to query the table to see if that mixID already exists, e.g.

Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand("SELECT COUNT(*) FROM MyTable WHERE MixID = @MixID", connection)

command.Parameters.AddWithValue("@MixID", mixID)
connection.Open()

If CInt(command.ExecuteScalar()) = 0 Then
    'No match found so the mix ID is new.
Else
    'Match(es) found so the mix ID already exists.
End If

connection.Close()

 

[Arg81]

awesome, that works a treat! Thanks for the help

 

[cjard]

Well, if some records will have null values then the unique constraint is out the window.

That's because SQL Server is crap.. If you asked someone at Oracle whether NULL = NULL they would say no, so logically any unique-constrained column can have any number of nulls because NULL is never equal to another NULL.

I found the following article to be an interesting discussion and implementation of a unique constraint that behaves correctly on SQLS2005:
http://blogs.msdn.com/sqlcat/archive/2005/12/20/506138.aspx

 

[cjard]

awesome, that works a treat! Thanks for the help

Dont forget, it's perfectly possible and logical to put that query into your MixTableAdapter... Add Query.. Select That Returns A Single Value.. It writes most of the SELECT COUNT(*) for you, just add the WHERE clause..

 

[Arg81]

Select That Returns A Single Value

that's true, and I'm actually in the process of doing that now

 

[Arg81]

that's actually helped out with another part of some validation I was needing to look into - 'tis great, 2 birds with 1 stone and all that!