Need help with conditional sql statement

MarkGT · May 5th, 2008, 07:02 PM

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?

mat41 · May 5th, 2008, 07:15 PM

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

MarkGT · May 5th, 2008, 07:21 PM

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

mat41 · May 5th, 2008, 07:30 PM

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

Wind is your friend
Matt
www.elitemarquees.com.au

mat41 · May 5th, 2008, 07:33 PM

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

dparsons · May 5th, 2008, 08:17 PM

: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
================================================== =========

mat41 · May 5th, 2008, 08:51 PM

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

MarkGT · May 6th, 2008, 12:42 PM

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")?

MarkGT · May 6th, 2008, 02:42 PM

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.

dparsons · May 6th, 2008, 02:59 PM

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
================================================== =========