SQL update statement with IF IS NOT NULL clause?

[omar999]

hi guys

im using classic asp and sql server 05 - and my web form submits an update statement which in result updates a sql table.

my question is, is there a way to force an update statement to only update by making use of the IF NOT IS NULL function?

my update statement

sSQL="UPDATE UK_SpecialsTEST SET " & _
"Price_Band_1=" & Price_Band_1 & ", Month_Band_1='" & Month_Band_1 & "', Date_Band_1='" & Date_Band_1 & "'," & _
"Price_Band_2=" & Price_Band_2 & ", Month_Band_2='" & Month_Band_2 & "', Date_Band_2='" & Date_Band_2 & "'," & _ 
"Price_Band_3=" & Price_Band_3 & ", Month_Band_3='" & Month_Band_3 & "', Date_Band_3='" & Date_Band_3 & "'," & _ 
"Price_Band_4=" & Price_Band_4 & ", Month_Band_4='" & Month_Band_4 & "', Date_Band_4='" & Date_Band_4 & "'," & _ 
"Price_Band_5=" & Price_Band_5 & ", Month_Band_5='" & Month_Band_5 & "', Date_Band_5='" & Date_Band_5 & "'  WHERE ID='" & ID & "' "

I need to somehow do a check on the Price_Band_1, Price_Band_2, Price_Band_3, Price_Band_4, Price_Band_5 fields of the web form to ONLY perform the SQL update if data is present in the form and submitted. otherwise ignore these empty form fields...

please advise and thanks in advance

[gpl]

Can you explain a bit more ... if data is present where ?
If you mean if data is supplied from your webform, then the code that generates the SQL is where you would determine whether or not to do the update

[omar999]

thanks for your reply - sorry for confusion, I have updated the first post.

but I basically meant that I only want to perform the sql update if there is data present in the web form fields. if there is nothing in a form field then I want to omit this field from updating in the sql statement.

is this possible?

[gpl]

Thanks for the update, then yes, your code that formulates the update statement would omit the relevant parts from the update if the field is empty, a bit like this

sSQL="UPDATE UK_SpecialsTEST SET "

if trim(Price_Band_1) >"" then sSQL=sSQL &  "Price_Band_1=" & Price_Band_1 & ","
if trim(Month_Band_1) >"" then sSQL=sSQL &  "Month_Band_1=" & Month_Band_1 & ","
if trim(Date_Band_1)  >"" then sSQL=sSQL &  "Date_Band_1="  & Date_Band_1  & ","

if trim(Price_Band_2) >"" then sSQL=sSQL &  "Price_Band_2=" & Price_Band_2 & ","
if trim(Month_Band_2) >"" then sSQL=sSQL &  "Month_Band_2=" & Month_Band_2 & ","
if trim(Date_Band_2)  >"" then sSQL=sSQL &  "Date_Band_2="  & Date_Band_2  & ","

and so on
but you will need to check that there is something supplied and the very last update must not have a "," after it, so you will need to delete that

[omar999]

i've tried your suggestion

sSQL="UPDATE UK_SpecialsTEST SET " & _
"if trim(Price_Band_1) >"" then sSQL=sSQL & Price_Band_1=" & Price_Band_1 & "," & _
"if trim(Month_Band_1) >"" then sSQL=sSQL & Month_Band_1=" & Month_Band_1 & "," & _
"if trim(Date_Band_1)  >"" then sSQL=sSQL & Date_Band_1="  & Date_Band_1  & "," & _
"if trim(Price_Band_2) >"" then sSQL=sSQL & Price_Band_2=" & Price_Band_2 & "," & _
"if trim(Month_Band_2) >"" then sSQL=sSQL & Month_Band_2=" & Month_Band_2 & "," & _
"if trim(Date_Band_2)  >"" then sSQL=sSQL & Date_Band_2="  & Date_Band_2  & " WHERE ID='" & ID & "' "

but I am getting Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'if'.
/prices-databasetest/update.asp, line 80

im not sure what I am doing wrong??

[ck9663]

There are a couple of options here...

1. Build your query string in such a way that if a data is missing, you don't include it on the query string, which is what GPL is trying to say (feel free to chime in if am wrong)...

2. In your UPDATE statement, use the CASE expression to update your table accordingly.

Happy Coding!!!

~~ CK

[omar999]

hi CK

I've tried the case expression within my update statement but I cant get it quite right...

sSQL="UPDATE UK_SpecialsTEST SET Price_Band_1 = CASE " & _
"WHEN Price_Band_1 = "" THEN Price_Band_1 = NULL WHERE ID=" & ID & "" 
"ELSE Price_Band_1 = " & Price_Band_1 & " WHERE ID=" & ID & ""  & _
"END"

I get this error;

Microsoft VBScript compilation error '800a0400'
Expected statement
/prices-databasetest/update.asp, line 54
"ELSE Price_Band_1 = " & Price_Band_1 & " WHERE ID=" & ID & "" & _
^

any suggestions please?

[ck9663]

Display the value of your variable sSQL on the screen and paste it here. We'll have a better idea of how your query string looks like.

~~ CK

[omar999]

do you mean what I'm entering in the form for the field Price_Band_1? If so im just entering any random integer...

[Clark Kent]

No.

Do a

response.write sSQL
response.end

Then post the query here. Let's go from there.

~~ CK

[omar999]

thanks CK - I understand now. ok response.write sSQL returns

UPDATE UK_SpecialsTEST SET Price_Band_1=66, Month_Band_1='Sep', 
Date_Band_1='NO FLIGHT',Price_Band_2=59, Month_Band_2='Oct', 
Date_Band_2='NO FLIGHT',Price_Band_3=343, Month_Band_3='Nov', 
Date_Band_3='NO FLIGHT',Price_Band_4=9, Month_Band_4='Dec', 
Date_Band_4='NO FLIGHT',Price_Band_5=9, Month_Band_5='Jan', 
Date_Band_5='NO FLIGHT' WHERE ID='37'

[ck9663]

The query you posted should work. Did it give any error?

~~ CK

[omar999]

the above query works because everything has a value. If for example I leave a Price_Band field empty the page errors

Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near ','.
/prices-databasetest/update.asp, line 75

I need the price_band fields to be ignored in the update statement or instead to enter NULL if nothing is entered...

please advise.

[ck9663]

I would do something like...

1. First check your form if at least 1 text is not empty. This should be done on the front-end script.

2. Build your query based on the content of your variable/form...like:

sSQL=     "UPDATE UK_SpecialsTEST SET "
if Not isnull(YourPrice_Band_1Variable) and YourPrice_Band_1Variable <> "" then
   sSQL = sSQL & "Price_Band_1 = " & YourPrice_Band_1Variable 

if Not isnull(YourMonth_Band_1Variable) and YourMonth_Band_1Variable <> "" then
   sSQL = sSQL & ",Month_Band_1 = " & YourMonth_Band_1Variable

That's a pseudo-code, so just follow the pattern...

Good Luck!!!

~~ CK

[gpl]

Omar
In post #4 I suggested you use basic to build up the SQL statement, determining if you want to update a column or not; however, your example put the IFs within strings. This wont work. The result of the basic should be a string of valid SQL, which is why CK suggested that you emit the resultant SQL string, you can then paste this into Query Analyser to test for validity.

Good luck with this, I used to have terrible trouble trying to work out what should go into a dynamic script, which bits were to build the script and which bits would finally be executed.

Personlly, I would populate the form with the columns from the database and write the whole lot back and not worry about whether they had been entered or not (if they were unchanged, the column would be updated with its original value).
gpl