Check if a database record already exists with this value

BrutalSnyper · Sep 25, 2012

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 · Sep 25, 2012

B4X:

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 · Sep 26, 2012

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

BrutalSnyper · Oct 3, 2012

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 · Oct 3, 2012

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 · Oct 4, 2012

Oops!

Bill Norris said:
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 · Oct 6, 2012

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

Check if a database record already exists with this value

BrutalSnyper

Member

mc73

Well-Known Member

BrutalSnyper

Member

BrutalSnyper

Member

Bill Norris

Active Member

Bill Norris

Active Member

BrutalSnyper

Member

Similar Threads

Check if a database record already exists with this value

BrutalSnyper

Member

mc73

Well-Known Member

BrutalSnyper

Member

BrutalSnyper

Member

Bill Norris

Active Member

Bill Norris

Active Member

BrutalSnyper

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency