Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 5th, 2008, 07:02 PM
Authorized User
 
Join Date: Apr 2008
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need help with conditional sql statement

I need to create a conditional sql statement. I don't know the syntax for putting "if...else" statements inside sql strings. Is that even possible or do I have to break up the sql and concatenate it with the "if...else" statements? I need to be able to update multiple dates in an access database, but the date fields also need to be able to remain empty. My current update statement works if dates are entered in every field. It does not work if any of the fields are blank. I understand why this is. I just don't know how to build the statement around it. Below is a piece of my sql statement (the whole thing is pretty long) and an idea of what I need to do.

Code:
dim r1, r2, r3, r4, r5, xemp_idx
.
.
.
xemp_idx=request.Form("xemp_idx")
r1=Request.Form("zEmp_idz")
r2=Request.Form("First_Name")  
r3=Request.Form("xLast_Namex") 
r4=Request.Form("Test1_Date")
r5=Request.Form("Test2_Date").
.
.

rsql = " UPDATE S6Test SET Emp_ID='"& r1 &"', First_Name='" & r2 & "', Last_Name='" & r3 & "',
Test1_Date= #" & r4 & "#, Test2_Date=#" & r5 & "# WHERE Emp_ID='"& xemp_idx &"';"
.
.
.

I was thinking about something along the lines of...

rsql = " UPDATE S6Test SET Emp_ID='"& r1 &"', First_Name='" & r2 & "', Last_Name='" & r3 & "'

if request.form("Test1_Date") <> "" then
r4=request.form("Test1_Date")
sql = sql & "Test1_Date=#" & r4 &"#"
else
sql = sql & "Test1_Date=null"
end if

if request.form("Test2_Date") <> "" then
r4=request.form("Test2_Date")
sql = sql & "Test2_Date=#" & r5 &"#"
else
sql = sql & "Test2_Date=null"
end if

sql= sql & "WHERE Emp_ID='"& xemp_idx &"';"

I know some of you aren't too keen on using null values, but they work for my purpose (simple is better right now). Ive tried what I typed above, but it doesn't work. So how could I build the sql statement to encorporate the "if...else" statement?
Reply With Quote
  #2 (permalink)  
Old May 5th, 2008, 07:15 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Yes it is possible EG:

dim r1, r2, r3, r4, r5, xemp_idx
r1 = ""
r2 = ""
r3 = ""
r4 = ""
r5 = ""
xemp_idx = ""

r1=trim(Request.Form("zEmp_idz"))
r2=trim(Request.Form("First_Name"))
r3=trim(Request.Form("xLast_Namex"))
r4=trim(Request.Form("Test1_Date"))
r5=trim(Request.Form("Test2_Date"))

rsql = " UPDATE S6Test SET Emp_ID='"& r1 &"', First_Name='" & r2 & "', Last_Name='" & r3 & "'"
if r4 <> "" then
   rsql = rsql & ",Test1_Date= #" & r4 & "#"
end if
if r5 <> "" then
   rsql = rsql & ",Test2_Date=#" & r5 & "#"
end if
rsql = rsql & " WHERE Emp_ID='"& xemp_idx &"';"

Wind is your friend
Matt
www.elitemarquees.com.au
Reply With Quote
  #3 (permalink)  
Old May 5th, 2008, 07:21 PM
Authorized User
 
Join Date: Apr 2008
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Excellent. Thank you, you've been a huge help on more than one occasion.

Quote:
quote:Originally posted by mat41
 Yes it is possible EG:

dim r1, r2, r3, r4, r5, xemp_idx
r1 = ""
r2 = ""
r3 = ""
r4 = ""
r5 = ""
xemp_idx = ""

r1=trim(Request.Form("zEmp_idz"))
r2=trim(Request.Form("First_Name"))
r3=trim(Request.Form("xLast_Namex"))
r4=trim(Request.Form("Test1_Date"))
r5=trim(Request.Form("Test2_Date"))

rsql = " UPDATE S6Test SET Emp_ID='"& r1 &"', First_Name='" & r2 & "', Last_Name='" & r3 & "'"
if r4 <> "" then
rsql = rsql & ",Test1_Date= #" & r4 & "#"
end if
if r5 <> "" then
rsql = rsql & ",Test2_Date=#" & r5 & "#"
end if
rsql = rsql & " WHERE Emp_ID='"& xemp_idx &"';"

Wind is your friend
Matt
www.elitemarquees.com.au
Reply With Quote
  #4 (permalink)  
Old May 5th, 2008, 07:30 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

No worries. You will find there are allot of very helpful people on this forum...

Wind is your friend
Matt
www.elitemarquees.com.au
Reply With Quote
  #5 (permalink)  
Old May 5th, 2008, 07:33 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

BTW you can shorten your code considerably if you got rid of all your r1, r2, r3, r4, r5, xemp_idx variables. You do realize the instead of using r1 you can just use trim(Request.Form("zEmp_idz")) ??

Obviously this is the case for all of them. Not only is the code shorter, its cleaner...


Wind is your friend
Matt
www.elitemarquees.com.au
Reply With Quote
  #6 (permalink)  
Old May 5th, 2008, 08:17 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

:shudders: Dynamic SQL >< Anywhooo.

Just glancing over this, there is a possible bug in the code provided in your where statement:
rsql = rsql & " WHERE Emp_ID='"& xemp_idx &"';"

In the first few lines you set the value of xemp_idx to that of an empty string and, from the code provided, you never give it a value so your code will evaluate to:

WHERE Emp_ID = '';

hth.
-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #7 (permalink)  
Old May 5th, 2008, 08:51 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Yes very true. Hopefully MarkGT still runs the following:

xemp_idx=request.Form("xemp_idx")

and this QS does have a value.

Wind is your friend
Matt
www.elitemarquees.com.au
Reply With Quote
  #8 (permalink)  
Old May 6th, 2008, 12:42 PM
Authorized User
 
Join Date: Apr 2008
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I used the r1, r2...etc because it made the sql statement easier for me to create/read. There are actually 27 fields being updated with the sql statment, I didnt post all of the code because it's really long.

While we're discussing sql statments...How would I create an sql statement with values from a <select> list? In my current sql statement I used request.form("xxxx") and retrieved the values based on the name of the input box, where "xxxx" was the name of the box. To create an sql statement, do I need to name the select box or the individual options? Would I still use the request.form("xxxx")?
Reply With Quote
  #9 (permalink)  
Old May 6th, 2008, 02:42 PM
Authorized User
 
Join Date: Apr 2008
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, I sort of figured this out. Below is a piece of code from my page. I have three separate select boxes that populate another select box, based on the selection from the first. I posted code for only one. The select boxes work fine. I need to get the values from the selections into an sql statement.

below is the code for one of my <select> lists
Code:
<select name="classyear" onChange="showYear()">
<option value="0">Choose year</option>

<% 
objrs.movefirst
while not objrs.eof%>
<option value="<%=objrs("class")%>"><%=objrs("class")%></option>
<%objrs.movenext
wend%>
</select>

<% 
objrs.movefirst
while not objrs.eof%>

<div id="<%=objrs("class")%>menu">
<select name="<%=objrs("class")%>month">
<%

strSQL = "SELECT distinct classmd from s6test where class='" & objrs("class") & "'"
set objrs1=objconn.execute(strSQL)
 
   while not objrs1.eof
%>

   <option value="<%=objRS1("classmd")%>"><%=objRS1("classmd")%></option>

   <%
   objrs1.movenext
   wend%>
</select>
</div>

<% 
objrs.movenext

wend %>
Here is the code for my sql statement:
Code:
wsql = " SELECT Count(Emp_ID) AS [numberfirstpasses] FROM S6Test where Score1>70 
AND class='"&classyear&"' AND classmd= '"&objrs("class")&"'&'&month&';"
set rs10 = objconn.execute (wSql)
The name of my second select box (that populates based on selections from the first) is dynamic. It's the bold portion of code above. I don't get any errors when I run this sql statement, but it doesn't return the correct result. It returns "0" when it should return "4." I assume it has to have something to do with the syntax of the <select> box values in the sql statement. Does anyone see a problem with the sql statement?

NOTE: The sql statement is one line in my asp page, I entered a return in the code above just to make it easier to read. The "month" at the end of the "<select name="<%=objrs("class")%>month">" is necessary because I use month, montha and monthb to differentiate between the three select lists.
Reply With Quote
  #10 (permalink)  
Old May 6th, 2008, 02:59 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Your SQL is wrong:

'"&objrs("class")&"'&'&month&'

Translates to
AND classmd = 'classname'&'month'

I think it should be:
'" & Request.Form(objrs("class") & month) & "';"

Which returns the value of whatever is selected in the drop down.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
Conditional IF statement iloveoatmeal Classic ASP Basics 5 July 20th, 2005 08:33 PM
Conditional SQL webasp1 Classic ASP Databases 3 March 4th, 2004 01:53 AM
conditional statement based on attribute value dancbishop XSLT 2 November 7th, 2003 12:50 PM
sql statement chrisangus@btinternet.com Beginning VB 6 2 June 23rd, 2003 04:28 PM



All times are GMT -4. The time now is 05:17 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.