Check if a database record already exists with this value

[BrutalSnyper]

Hi there,

I've got a database that the user can store data to, but I would like to make it so if there is already a record with the data they enter then it stops them from entering it.

E.G.

Database contains record with the value "Example", user tries to create new record with value "Example", application then informs the user that that value already exists.

 

[mc73]

Try
Astring=sql.execquerysingleresult2("select yourfield from your table where yourstring=?", array as string("example"))
Msgbox("found","ok")
Catch 
Sql.execnonquery2("insert into your table values (?,?,...)", array as object (field1, field2,...))
End try

Can't remember the exact syntax of the statements above but I think you can work it out.
There are are other ways to accomplish this as well.

 

[BrutalSnyper]

Thank you mc73, I'll see if I can get it working with that!

 

[BrutalSnyper]

The way mc73 suggested ended up being very complicated and requiring too much change to my existing code. Could anybody suggest an alternative way please?

 

[Bill Norris]

RE: Try This

Dim sql1 as sql
Dim cusor1 as cursor

cursor1=sql1.executequery("select [FIELDNAME] from

where [FIELDNAME]=" & (the value you are looking for)


if cursor1.rowcount>1 then... (matching value found)
msgbox("Record Already Exists","")
else
(insert code to add the record)

end if

NOTE: if the value you are looking for is numeric, you can simply put the variable containing that value or the text property of the view containing the value in place of (the value you are looking for). If it is a text value you will have to enclose in quotations. You may have to play around the the quotation marks to get the syntax just right. Hope that all makes sense.

 

[Bill Norris]

Oops!

Dim sql1 as sql
Dim cusor1 as cursor

cursor1=sql1.executequery("select [FIELDNAME] from

where [FIELDNAME]=" & (the value you are looking for)


if cursor1.rowcount>1 then... (matching value found)
msgbox("Record Already Exists","")
else
(insert code to add the record)

end if

NOTE: if the value you are looking for is numeric, you can simply put the variable containing that value or the text property of the view containing the value in place of (the value you are looking for). If it is a text value you will have to enclose in quotations. You may have to play around the the quotation marks to get the syntax just right. Hope that all makes sense.

Should be:
if cursor1.rowcount>0 then... sorry!

 

[BrutalSnyper]

Thank you very much for your help Bill, it all made perfect sense and was very helpful!