Update or Insert 2

[apex82]

I have the following sql statement in ASP:

sql = "UPDATE [System_Terms] SET "
sql = sql & "[Terms Text]='" & Request.Form("EditTermsText") & "'"
sql = sql & " WHERE [SystemID] = '" & id & "'"

Basically this works perfectly when a record exists, can this be easily tweaked to INSERT as well if a record doesn’t exist?

Thanks.

 

[RiverGuy]

IF EXISTS (SELECT 1 FROM [System_Terms] WHERE [SystemID] = 1)
	UPDATE a
	SET a.[Terms Text] = 'abc'
	FROM [System_Terms] a
	WHERE [SystemID] = 1
ELSE
	INSERT INTO [System_Terms] 
	([SystemID], [System_Terms])
	SELECT 'abc', 1

Obviously, turn it into a string, and replace my hard-coded values with your variables.

 

[markros]

What is your SQL Server version? If it's SQL Server 2008, look into MERGE command in BOL (the syntax is a bit complex). If not, then I would go with stored procedure using logic shown by RiverGuy.

 

[apex82]

Great, thanks!

I’ve tried to convert this and I now have the INSERT working but not the UPDATE

IF EXISTS then 
	sql= "SELECT [SystemID] FROM [System_Terms] WHERE SystemID = '" & id & "' "
	sql=sql & "UPDATE a "
   	sql=sql & "SET a.[Terms Text] = '" & Request.Form("EditTermsText") & "',"
  	sql=sql & "FROM [System_Terms] a "
  	sql=sql & "WHERE [SystemID] = '" & id & "'"
	ELSE
	sql = "INSERT INTO [System_Terms] ([SystemID], [Terms text] )" 
	sql = sql & " VALUES "
	sql = sql & "('" & id  & "', '" & Request.Form("EditTermsText") & "')"
  END IF

Can you see where I might be going wrong?

 

[apex82]

Am using SQL Server 2008

 

[gmmastros]

try this:

sql=""
sql=sql & " IF EXISTS (SELECT 1 FROM [System_Terms] WHERE SystemID = '" & id & "') "
sql=sql & "   UPDATE a "
sql=sql & "   SET    a.[Terms Text] = '" & Request.Form("EditTermsText") & "'"
sql=sql & "   FROM   [System_Terms] a "
sql=sql & "   WHERE  [SystemID] = '" & id & "'"
sql=sql & " ELSE "
sql=sql & "   INSERT INTO [System_Terms] ([SystemID], [Terms text] )" 
sql=sql & "   VALUES "
sql=sql & "   ('" & id  & "', '" & Request.Form("EditTermsText") & "')"

BTW. You should NEVER directly use form variables in a sql query. This leaves you wide open to [google]SQL Injection[/google]. Please read up on this immediately, before something truly devastating happens.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[apex82]

Thanks, yes that works.

I will have a read up on SQL injection too!

 

[markros]

Since you're using SQL Server 2008, you don't need the syntax you were shown. Please follow up on my suggestion of using MERGE.

 

[gmmastros]

you don't need the syntax you were shown

nope. you don't need the syntax. You don't need the MERGE syntax either. There's probably a dozen different ways this can be done. Use which ever version makes the most amount of sense to you, because you're likely the person that will need to support this code later.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

The other common way is to do an UPDATE first and the if @@ROWCOUNT=0 do an INSERT.

 

[ItHurtsWhenIThink]

I have used:

set rs = Server.CreateObject("ADODB.Recordset")
sqlstatement="SELECT * FROM table Where ID=" & ID
rs.Open sqlstatement, DbString,1,3

If rs.EOF Then
rs.AddNew
rs("XX")=XX
rs("XXX")=XXX
rs("XXXX")=XXXX
Else
rs("XXX")=XXX
rs("XXXX")=XXXX
End If
rs.Update
rs.close
set rs = nothing

 

[JoeAtWork]

I stopped using recordsets for INSERTS, DELETES, and UPDATES years ago. They are inefficient and prone to error.

In the case of updates and deletes, you are unnecessarily bringing over all the records to populate the recordset. Lets say you are updating/deleting 1000 records, that's a lot of network traffic for no good reason. If you just send it the UPDATE or DELETE statement that's far less network traffic. INSERTs aren't as bad regarding the network traffic (assuming you had the wits to use a filter so no existing records come across), but writing the statement yourself means you know exactly what you are sending to the server - making it easier to debug. If you use a recordset, you don't know what sort of SQL statement is being sent to the server (unless you use SQL Profiler).

 

[danvlas]

apex82, create a stored procedure with parameters.
Instantiate the procedure from your ASP code using the ADODB.Command object.
Pass in the parameters through Request.Form("WhateverValue")
Execute the procedure and grab the result you need (if any).

Create Proc AddOrUpdate
@TermsText varchar(1000)
,@SystemID int

As
Set NOCOUNT ON
--HERE COMES RiverGuy's code:

IF EXISTS (SELECT 1 FROM [System_Terms] WHERE [SystemID] = @SystemID)
UPDATE a
SET a.[Terms Text] = @TermsText
FROM [System_Terms] a
WHERE [SystemID] = @SystemID
ELSE
INSERT INTO [System_Terms]
([SystemID], [System_Terms])
SELECT @TermsText, @SystemID


In your ASP code:

Set cmd=CreateObject("ADODB.Command")
Set cmd.ActiveConnection = YourASPConnection
cmd.CommandText="AddOrUpdate"
cmd.CommandType = 4
cmd.Parameters.Refresh
cmd.Parameters("@TermsText")=Request.Form("EditTermsText")
cmd.Parameters."(@SystemID") = id
cmd.Execute

This way you're fully protected against SQL Injection, you benefit from lowest network traffic and you can pass in texts that contain apostrophes without getting a syntax error.

Basically that's what George said.


Daniel Vlas
Systems Consultant