SQL – IF Exists Update Else Insert

0 pts.
Tags:
SQL
SQL Server
Web site design & management
I have searched far and wide but have not been able to solve this problem. I am still in the early learning stage and this is the most complex issue I've come across so far. I have a form that when submitted needs to either update fields in a table or insert new records. I originally created a form to insert records which worked fine. I now have to add in the ability to update. What I have is not even close and I now that. I have tried many things but I'm basically stumped. I've pasted it below: Dim sParamValue as String = String.Empty Dim sKeyName as String = String.Empty Dim conNCIGFDNN As SqlConnection Dim strInsert As String Dim strUpdate As String Dim cmdInsert As SqlCommand Dim cmdUpdate As SqlCommand For i As Integer = 0 to Request.Form.Keys.Count - 1 sKeyName = Request.Form.Keys(i) sParamValue = Request.Form(sKeyName) Next Dim UserID As String = Request.Form("UserID") Dim OrgName As String = Request.Form("OrgName") Dim ContactName As String = Request.Form("ContactName") Dim Position As String = Request.Form("Position") Dim email As String = Request.Form("email") Dim Address1 As String = Request.Form("Address1") Dim Address2 As String = Request.Form("Address2") Dim City As String = Request.Form("City") Dim ST As String = Request.Form("ST") Dim RespStates As String = Request.Form("RespStates") Dim Zip As String = Request.Form("Zip") Dim Phone As String = Request.Form("Phone") Dim Fax As String = Request.Form("Fax") Dim Website As String = Request.Form("Website") If (dnn_KB_XMod_Index_VC500UserID = dnn_Users.UserID) Then conNCIGFDNN = New SqlConnection("Server=server;UID=uid;PWD=pass;database=dn") strUpdate = "UPDATE dnn_KB_XMod_Index_VC500 (UserID, OrgName, ContactName, Position, email, Address1, Address2, City, ST, RespStates, Zip, Phone, Fax, Website) Values ('" & UserID & "','" & OrgName & "','" & ContactName & "','" & Position & "','" & email & "','" & Address1 & "','" & Address2 & "','" & City & "','" & ST & "','" & RespStates & "','" & Zip & "','" & Phone & "','" & Fax & "','" & Website & "')" cmdUpdate = New SqlCommand(strUpdate, conNCIGFDNN) conNCIGFDNN.Open() cmdUpdate.ExecuteNonQuery() conNCIGFDNN.Close() Else conNCIGFDNN = New SqlConnection("Server=server;UID=uid;PWD=pass;database=db") strInsert = "Insert dnn_KB_XMod_Index_VC500 (UserID, OrgName, ContactName, Position, email, Address1, Address2, City, ST, RespStates, Zip, Phone, Fax, Website) Values ('" & UserID & "','" & OrgName & "','" & ContactName & "','" & Position & "','" & email & "','" & Address1 & "','" & Address2 & "','" & City & "','" & ST & "','" & RespStates & "','" & Zip & "','" & Phone & "','" & Fax & "','" & Website & "')" cmdInsert = New SqlCommand(strInsert, conNCIGFDNN) conNCIGFDNN.Open() cmdInsert.ExecuteNonQuery() conNCIGFDNN.Close() End If Any help is very appreciated as I'm sort of 'dead in the water' here.
ASKED: February 28, 2007  3:41 PM
UPDATED: February 28, 2007  9:38 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

First, why are you creating the connection, executing one SQL, and then dropping the connection? Normally, you’d open the connection once, and leave it open for the duration of the application.

Next, make a stored procedure (see CREATE PROCEDURE) which does the following:
UPDATE the existing record
If the number of affected rows is zero
INSERT the data
End if

The number of affected rows is gotten with GET DIAGNOSTICS in DB2, SQL%ROWCOUNT in Oracle, or @@ROWCOUNT in MS SQL Server.

Call the stored procedure from your code. Rather than using DIM to set up single-use string variables, you can just pull from the form directly, since you’ll only need each item once.

The form of the call is:
DB2: CALL procedureName(arg1, arg2, … argn)
Oracle: BEGIN procedureName(arg1, arg2, … argn); END;
MS SQL Server: EXEC procedureName arg1, arg2, … argn

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linker.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SheldonLinker
    I just noticed that you also had the update syntax wrong. Update is: UPDATE table SET field1=value1, field2=value2, ... fieldN=valueN WHERE keyField=keyValue
    30 pointsBadges:
    report
  • Wolfman24
    Thank you, SheldonLinker! Your suggestions helped clear things up for me. I was not sure how to structure the UPDATE statement as I'd seen a couple different ways on various sites. It looks like I was definitely not going about this the best way. I'll apply these suggestions tomorrow. Again, thanks for your help.
    0 pointsBadges:
    report
  • Wolfman24
    Thank you, SheldonLinker! Your suggestions helped clear things up for me. I was not sure how to structure the UPDATE statement as I'd seen a couple different ways on various sites. It looks like I was definitely not going about this the best way. I'll apply these suggestions tomorrow. Again, thanks for your help.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following