about sql database

[gjoisa]

I have one sql database in my program. My database contains 9 fields . My database is about latitude longitude of places . Ex : Mumbai , 18 , 58 , N , 72 , 50 , E , 5 , 30 (Placename,lat degree,lat min, lat direction,long degree,long min,long direction,timezone hour,timezone minute) . The problem is , Database accepts the same data if it already exists in database . My requirement is , it should not accept the field1 data if it already exists .:sign0085:

 

[glook]

If field1 is set as the PRIMARY KEY or a UNIQUE column, then it will not accept duplicate values. Personally I think it is best practice to test for duplicates before attempting an insert, so that you can nicely control the action. This is easily done with a small select:

cmd.CommandText = "SELECT ROWID from My_table WHERE Name = '" & txtName.Text & "'"
reader.Value = cmd.ExecuteReader
havedup = reader.ReadNextRow
reader.Close
If havedup Then
Msgbox(txtName.Text & " already exists!" & crlf & "Must be unique.",,cMsgBoxHand)
Return
End If

Geoff.

 

[gjoisa]

I couldnot understand completely . can you please explain more with fully functioning example ?

 

[glook]

I'll try to explain (sorry about the delay - been busy with other things).

A column may be defined as either the PRIMARY KEY or as UNIQUE. This is usually done when you create the table, either by an SQL command from B4ppc or with an external utility.

This column will then not accept duplicate values. If you try to insert record where the column value already exist, an "error" message will be produced - something like "Abort due to constraint violation column field1 is not unique".

In my opinion, it is best to handle this event before the error occurs, by testing for the value before attempting any insert - as in the sample code. Your program can then take the appropriate action. This test could be done regardless of how the column is defined.

Hope that helps,
Geoff.

 

[giannimaione]

try it

a little example

 

[glook]

Thanks Gianni, an excellent example.