Validating record does not exist before inserting

[cawthor]

Hi,

I have a web form that once submitted, writes a record to a table. Is there a way that I can check if the record already exists before my form submits? Eg. ideally, user fills out form, clicks on submit and either gets an error that the record exists, or the record is inserted and a 'thank you' message displayed. Maybe the initial submit launches a new window that checks if the record exists based on the form variables. If it exists it displays an error, else it submits the original form for insertion. I don't even know if this is possible...or how to go about solving this.

Any help would be much appreciated!

 

[DotNetGnat]

You can do something like this....

sql="SELECT RecordID FROM yourtable WHERE Username='"&request("uname")&"' AND field1='"&request(f1)&"' "

rs=conn.execute(sql)

if rs.EOF And rs.BOF then

'record not found

else

'record found.

-DNG

 

[DotNetGnat]

oops...missed "end if"

the above is just a sample...but the main idea is to find out whether a record id exists for the same data that user is trying to input again....thats what we are checking in our where condition...observe that we are using AND condition in our where clause to get a complete match...

so if the where clause gets matched then the record id will be returned and you will get a message saying record not found and vice versa...

-DNG

 

[Sheco]

How will you know if a record already exists? Is there some key value that you can check?

Maybe something like this:

Set rs = cn.Execute("SELECT Count(*) as KeyCount WHERE KeyField = '" & TheNewValue & "'")
IF rs(0) = 0 then
  'Does not exist
Else
  'Does exist
End If

Also, unless it will always be a short table and there is no need for much security then you probably don't want to make the browser aware of every existing key in the table. So you'll need a page transition of some sort.

You could perhaps put code like above into a page with no interface and call it from client-side script using the xmlhttp object... that would be better than trying to launch a separate window.

 

[arst06d]

Or, just attempt the insert anyway and capture the "record already exists" error, displaying a Thank you or Error message as appropriate