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
14 8830
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
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?
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
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??
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
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?
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
do you mean what I'm entering in the form for the field Price_Band_1? If so im just entering any random integer...
No.
Do a -
-
response.write sSQL
-
response.end
-
-
Then post the query here. Let's go from there.
~~ CK
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'
The query you posted should work. Did it give any error?
~~ CK
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.
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Don |
last post by:
Hi,
I am moving from Sybase to Oracle and I used to be able to do update
statement like this in Sybase:
UPDATE TABLE1
SET T1.field1 = T2.field2
FROM TABLE1 T1, TABLE2 T2
WHERE T1.field2...
|
by: pb648174 |
last post by:
I have a single update statement that updates the same column multiple
times in the same update statement. Basically i have a column that
looks like .1.2.3.4. which are id references that need to...
|
by: serge |
last post by:
/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an...
|
by: Vorpal |
last post by:
Here is a small sample of data from a table of about 500 rows
(Using MSSqlserver 2000)
EntryTime Speed Gross Net
------------------ ----- -----
21:09:13.310 0 0 0
21:09:19.370 9000 ...
|
by: HeadScratcher |
last post by:
I am trying to speed up my update statements by removing inner select
statements.
Example:
update orders set shipname = (select contactName from
customers where customerid = orders.customerID)...
|
by: Sean Shanny |
last post by:
To all,
The facts:
PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI
drives in hardware RAID 0 configuration. Database size with indexes is
currently 122GB. Schema for...
|
by: Dima Gofman |
last post by:
I have a trigger on UPDATE on a table. I'm running some maintenance
UPDATE and DELETE queries which I want the trigger to ignore but at the
same time I want other UPDATE queries that other users...
|
by: Steve |
last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement
I got ASP error number 13 when I use the SELECT...FOR UPDATE statement
as below.
However, if I use SELECT statement without...
|
by: pbd22 |
last post by:
hi.
I am having probelms with an update statement. every time
i run it, "every" row updates, not just the one(s) intended.
so, here is what i have. i have tried this with both AND and OR
and...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |