SQL – IF Exists Update Else Insert
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.
Software/Hardware used:
Software/Hardware used:
ASKED:
February 28, 2007 3:41 PM
UPDATED:
February 28, 2007 9:38 PM
Answer Wiki:
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)
To see all answers submitted to the Answer Wiki: View Answer History.
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
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.
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.