IF Exists

[DirtDawg]

Hello all,

I need some help with if exists (select * statement. I am trying to use if it exists to update and if the record does not exist to insert it. I am tyring to use asp to MSSQL 7 server. Here is the code below

strwkn = Request.form("wkn&quot
strsrv = request.form ("srv&quot
strvslvoy = Request.form("vslvoy&quot
stretd = Request.form("etd&quot

SET db = Server.CreateObject("ADODB.Connection&quot
db.Open "DSN=DSN", "id","pass"
SQL = if exists(select * from vsl where wkn = '" & strwkn & "' and srv = '" & strsrv & "')
"Update vsl set vslvoy='" & strvslvoy & "' Where wkn='" & strwkn & "' and srv='" & strsrv & "', and etd='" & stretd & "'"
ELSE
"Insert into vsl (wkn, vslvoy, srv, etd)Values ('" & strwkn & "', '" & strvslvoy & "', '" & strsrv & "', '" & stretd & "')"


Thanks in advance

 

[SonOfEmidec1100]

You have a comma in the where clause, remove it the rest looks ok at first glance

and srv='" & strsrv & "', and etd='" & stretd & "'"

and srv='" & strsrv & "' and etd='" & stretd & "'"

 

[SonOfEmidec1100]

Also Not sure about ASP but from a VB point of view you need to build the string correctly (quotes and contiuation chars).

SQL = "if exists(select * from vsl where wkn = '" & strwkn & "' and srv = '" & strsrv & "')" _ &
"Update vsl set vslvoy='" & strvslvoy & "' Where wkn='" & strwkn & "' and srv='" & strsrv & "', and etd='" & stretd & "'" _ &
" ELSE " _ &
"Insert into vsl (wkn, vslvoy, srv, etd)Values ('" & strwkn & "', '" & strvslvoy & "', '" & strsrv & "', '" & stretd & "')"

 

[SonOfEmidec1100]

3rd attempt , you cant use select * with if exists, you must select one field (say wkn), also your where clauses should be the same in the Select and Update statements.

Thats it from me.

 

[DirtDawg]

SonOf,

I tried what you said but I am still getting errors on the Select statement, I also read a little more about it and found out that it only drops tables. Is this true?

Thanks

 

[JohnDTampaBay]

The best answer is to write a stored procedure that accepts a few parameters (strwkn, strvslvoy, strsrv, and stretd) and determines whether to insert or update, then call the procedure from ASP. That way there is no question of the SQL being executed.

The EXISTS predicate is only concerned if a row is or is not returned from the sub-query, not what columns are included in that row. You can use SELECT * or SELECT Column or SELECT 1. SQL only cares if a row EXISTS or not. BOL shows examples of of all three.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.