• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

How do I check for a SQL server record before attempting and update?

I have an application that updates an SQL Server Express database with values from a web form. This is done in the code behind VB in the button_click event. How do I check for an existing record with a value the same as the one trying to be created. Currectly, if someone trys to create a new record with the same name as a record that already exists the user gets a funky error message and bombed out of the web page. I just want to warn them that the records already exists and send them back to the web form to try again.

I need a good code example of how to do this.
0
apwbe
Asked:
apwbe
  • 3
  • 2
1 Solution
 
tobzzzCommented:
The below isn't exact code but gives you a good idea... you obviously have to put connect to db strings in etc...

isArray() is VB and just tells you if any results are found whatsoever, if so obviously throw an error, if not continue and UPDATE as you already are.
do a SELECT statement - maybe in a function.
 
function checkUserExists()
    checkUserExists = SELECT username FROM users WHERE username='request.form("user")'
end function
 
then in the ASP page do:
 
if isArray(checkUserExists)<>"" then
    response.write "user exists"
else
    UPDATE users SET username=request.form("user")
end if
    

Open in new window

0
 
apwbeAuthor Commented:
Would the fuction be put into the default.aspx page or the default.aspx.vb codebehind page?

in particular I  have a SQL Server Express database (Whos_Who.dbo) and a table called "Who" that has "Name" as a primary key. I only want one record per person so when someone trys to input a new record using a name value that already exist they can not insert the new record. The insertion takes place on the button click event and coded on the aspx.vb page. I would like to use an error popup message to alert the user that the record already exists an then upon them acknowledging the fact take them back to the web form.  which is all written in ASP on the default,aspx page.
0
 
apwbeAuthor Commented:
PS currently when someone trys to insert a record that already exists they get "violation of Primary Key constraint 'PK_Who" Cannot insert duplicate key in object 'dbo.who' . Do you know of a way to use this error code to my advantage when trying to accomplish what I have described. Maybe I am going about this all wrong and should be using some kind of custom error trapping and messaging.

Any ideas?
0
 
tobzzzCommented:
If you submit the form to the same page you're working on, above the HTML in ASP tags do the select lookup, if isArray() exists then show a javascript alert which once clicked returns you back a page to your form so you can change your form to a value that doesn't exist. See code example below:
If isArray(yourSELECTlookup)<>"" then
    OnLoadString="alert('Sorry, entry already exists, try again'); document.location='javascript:history.go(-1)'"
Else
    UPDATE STATEMENT
End If
 
<BODY onLoad="OnLoadString">
 
</BODY>

Open in new window

0
 
apwbeAuthor Commented:
I never really got this to work but the code looked correct. thanks for the effort and I am sorry I took so long.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now