select case structure syntax

[Kris912]

I am gettin an error "No value given for one or more required parameters" when using a select case structure to query the database based on the first letter in the title. I could have sworn it was working fine yesterday. Can someone tell me if this is sytactically correct?

case "S"

sql = "select * from Clients"
sql = sql & " where Title like '"& choice & "%'"
sql = sql & " order by Title ASC"
set rs = connection.execute(sql)

Thanks
Kris

 

[DotNetGnat]

do a response.write sql to see the actual query being passed...may be choice is empty...

-DNG

 

[DotNetGnat]

also post your complete code...the error you are getting is not related to the code snippet that you posted here...

-DNG

 

[Kris912]

DNG--

I did the response.write sql This is what I got...

select * from Clients where C_Title like 'S%' order by C_Title ASC

Here is the entire code:

<%
set connection = server.createobject("ADODB.Connection")
connection.open session("DBConnString")
%>

<%
choice = request.queryString("choice")

Select Case (choice)
case "A"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "B"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "C"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "D"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "E"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "F"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "G"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "H"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "I"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "J"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "K"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "L"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "M"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "N"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "O"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "P"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
set rs = nothing
connection.close
case "R"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "S"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
response.write sql
case "T"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "U"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "V"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "W"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)
case "Y"
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice & "%'"
sql = sql & " order by C_Title ASC"
set rs = connection.execute(sql)

End Select
%>
<%
'response.write(choice)
%>

<%
if request.QueryString("choice") = "" then
response.Write("Please make a selection and click view!")
else
While Not RS.EOF
C_Title = RS("C_Title")
response.Write Title & "<br>"
counter = counter +1
RS.MoveNext
Response.Flush()
WEND
if C_Title = "" then
response.Write"<strong>"& ("There are no clients beginnig with") & " " & request.QueryString("choice") & "<strong>"
end if
end if
%>
</div></td>
</tr>
</table>

Thanks DotNetGnat

 

[DotNetGnat]

lets modify your whole code...you dont need select case here..try this and tell me what error you get..

<%
set connection = server.createobject("ADODB.Connection")
connection.open session("DBConnString")
choice = request.QueryString("choice")                
                   
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice& "%'"                                            
sql = sql & " order by C_Title ASC"

set rs = connection.execute(sql)

if request.QueryString("choice") = "" then
response.Write("Please make a selection and click view!")
else
 While Not RS.EOF
C_Title= RS("C_Title")

response.Write Title & "<br>"
counter = counter +1
 RS.MoveNext
 Response.Flush()                                                         WEND
if C_Title = "" then
                                             response.Write"<strong>"& ("There are no clients beginnig with") & " " & request.QueryString("choice") & "<strong>"                                     
end if
end if
%>                                
 </div></td>
</tr>
</table>

-DNG

 

[DotNetGnat]

ok this one:

<%
set connection = server.createobject("ADODB.Connection")
connection.open session("DBConnString")
choice = request.QueryString("choice")                
                   
sql = "select * from Clients"
sql = sql & " where C_Title like '"& choice& "%'"                                            
sql = sql & " order by C_Title ASC"

set rs = connection.execute(sql)

if request.QueryString("choice") = "" then
response.Write("Please make a selection and click view!")
else
 While Not rs.EOF
C_Title=rs("C_Title")
response.Write C_Title & "<br>"
counter = counter +1
 rs.MoveNext
 Response.Flush()
WEND
if C_Title = "" then
response.Write"<strong>"& ("There are no clients beginnig with") & " " & request.QueryString("choice") & "<strong>"                                     
end if
end if
%>                                
 </div></td>
</tr>
</table>

-DNG

 

[Kris912]

Thanks DNG...I found my stupid mistake! "C_Title Not Title"
After a while go blind looking at this stuff. A pair of fresh eyes usually does the trick! It working fine though. I will definetly try it your way--a lot less code! I will get back to you and let you know...

 

[DotNetGnat]

i changed that in my code...i observed that you were using Title instead of C_Title...and you dont need select case...

-DNG

 

[Kris912]

Why didn't I think that request.querystring("choice") was enough?? You just saved me 121 lines of code ;-)

Thanks,
Kris

 

[DotNetGnat]

no problem...glad to be of help...

-DNG