Multiple condtional SQL statements: IF NOT EXISTS

Hi there,

I'm trying to insert/update a record, if it meets two conditions. I have two variables which get a querystring and an ip number. First I want to check if the querystring is already in the database. If not, insert the record. If the querystring is there, check if the ip number is already in the database. If not, insert the record into the database. I now have the following code: (MS-SQL / ASP.net 2.0 VB.net)

' Conditional INSERT command
        getStatCmd = _
            "IF NOT EXISTS (SELECT * FROM tblReferers WHERE refQueryString = '" & myQueryString & "') " & _
            "IF NOT EXISTS (SELECT * FROM tblReferers WHERE refIpAddress = '" & myRemoteAddr & "') " & _
            "INSERT INTO tblReferers(refPageAccessed,refQueryString,refIpAddress,refReferer) " & _
            "VALUES(" & _
             "'" + myUrl + "'," & _
             "'" + myQueryString + "'," & _
             "'" + myRemoteAddr + "'," & _
             "'" + myHttpReferer + "')"

        ' Conditional UPDATE command
        updStatCmd = _
            "UPDATE tblReferers " & _
            "SET refCountIn = refCountIn + 1" & _
            "WHERE refQueryString = '" & myRef & "'"

I keep getting syntax errors when playing with the insert command. Any ideas?

Thanks!

LVL 4
RineauAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

satish_nagdevCommented:
first advise dont use queries like that i.e. write queries in code(vb.net/c# here)
second i advise you to write a stored procedure to do the job pass the parameters then do something like following

IF NOT EXISTS (SELECT * FROM tblReferers WHERE refQueryString = @QueryString or refIpAddress = @IPAddres)
INSERT INTO tblReferers(refPageAccessed,refQueryString,refIpAddress,refReferer)
VALUES(@myUrl ,@myQueryString ,@myRemoteAddr ,@myHttpReferer ')
else write the update part.

thanks,
satish.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RineauAuthor Commented:
Thanks Satish,

I've never worked with stored procedures before. I guess it has some advantages when it comes down to performance and security, though I heard that you only need to write them when you actually need them. I do not know if that is the case here?

Anyway, here's my sp:

ALTER PROCEDURE sdstats.spGetReferers
      
      (
      @myUrl varchar(75),
      @myQueryString varchar(50),
      @myRemoteAddr varchar(12),
      @myHttpReferer varchar(155)
      )
      
AS
      BEGIN
      SET NOCOUNT ON
      
      IF NOT EXISTS
            (SELECT * FROM tblReferers WHERE refQueryString = @myQueryString or refIpAddress = @myRemoteAddr)
             INSERT INTO tblReferers(refPageAccessed,refQueryString,refIpAddress,refReferer)
             VALUES(@myUrl ,@myQueryString ,@myRemoteAddr ,@myHttpReferer)
      
      END
      RETURN

I am not sure where to declare the variables though... Am I on the right track?
0
jogosCommented:
With the procedure-declaration you're OK. The parameter-list is the declaration of the variables.

Calling your procedure can be done with variables or constants. First try to test it in SQL, afterwarts call it from your code

exec  sdstats.spGetReferers    @myUrl ,    @myQueryString,      @myRemoteAddr,     @myHttpReferer
OR
exec  sdstats.spGetReferers    "my URL HERE" ,    "my Query String...",     "my Remote Addr.....",     "HTTP:..myHttpReferer"


0
satish_nagdevCommented:
yea procedures have edge in terms of performance as far as i've heard and felt also practically in my last project.
well i prefer to use them wherever possible one reason is
software/apps change over time in some cases say you've same work to do at many places wouldnt it be good to have a SP and reduce the work.

happy coding.

cheers,
satish.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.