Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. 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 Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 24th, 2003, 06:23 PM
Registered User
 
Join Date: Jul 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select Case

Can anyone help me out on a problem I have with my coding?

I am trying to display two different sets of results using "Select Case". This is linked from a drop down box on another page.
The first "Case" statement works but the second "Case" statement does not. I have tried to manipulate the code and I can show one or the other results but not both.
Thanks for reading this problem.

The code I have is below:

<%
  varProductType = Request.Form("producttype")

  Select Case varProductType

    Case "Mains Drills"

  Dim objRS, objComm, objParam, strType
  Set objComm = Server.Createobject("ADODB.Command")

  objComm.ActiveConnection = strConnect
  objComm.CommandText = "qryMainsDrills"
  objComm.CommandType = adCmdStoredProc

  Set objParam = _
     objComm.CreateParameter("Required Type", adVarChar, adParamInput, 50)
  objComm.Parameters.Append objParam

  strType = "Mains"
  objComm.Parameters("Required Type") = strType

  Set objRS = objComm.Execute

  Set objComm = Nothing
  Set objParam = Nothing

Response.Write "<TABLE BORDER=""0"" CELLSPACING=""6"" CELLPADDING=""3"">" & _
"<tr>" & _
"<td WIDTH=80>" & "&nbsp;" & "</td>" & _
"<td WIDTH=100>" & "<B>Product ID</B>" & "</td>" & _
"<td WIDTH=120>" & "<B>Manufacturer</B>" & "</td>" & _
"<td WIDTH=170>" & "<B>Description</B>" & "</td>" & _
"<td WIDTH=150>" & "<B>Product Code</B>" & "</td>" & _
"<td WIDTH=70>" & "<B>Price</B>" & "</td>" & _
"</tr>"

  While Not objRS.EOF
    Response.Write _

  "<TR>" & _
    "<TD>" & objRS("Image") & "</TD>" & _
    "<TD ALIGN=CENTER>" & objRS("ProductID") & "</TD>" & _
    "<TD>" & objRS("Manufacturer") & "</TD>" & _
    "<TD>" & objRs("ProductDescription") & "</TD>" & _
    "<TD>" & objRS("ProductCode") & "</TD>" & _
    "<TD>" & FormatCurrency(objRS("UnitPrice")) & "</TD>" & _
  "</TR>" & _

    objRS.MoveNext
  Wend
Response.Write "</TABLE>"
  objRS.Close
  Set objRS = Nothing


    Case "Cordless Drills"

  objComm, objParam, strType
  Set objComm = Server.Createobject("ADODB.Command")

  objComm.ActiveConnection = strConnect
  objComm.CommandText = "qryCordlessDrills"
  objComm.CommandType = adCmdStoredProc

  Set objParam = _
     objComm.CreateParameter("Required Type", adVarChar, adParamInput, 50)
  objComm.Parameters.Append objParam

  strType = "Cordless"
  objComm.Parameters("Required Type") = strType

  Set objRS = objComm.Execute

  Set objComm = Nothing
  Set objParam = Nothing

Response.Write "<TABLE BORDER=""0"" CELLSPACING=""6"" CELLPADDING=""3"">" & _
"<tr>" & _
"<td WIDTH=80>" & "&nbsp;" & "</td>" & _
"<td WIDTH=100>" & "<B>Product ID</B>" & "</td>" & _
"<td WIDTH=120>" & "<B>Manufacturer</B>" & "</td>" & _
"<td WIDTH=170>" & "<B>Description</B>" & "</td>" & _
"<td WIDTH=150>" & "<B>Product Code</B>" & "</td>" & _
"<td WIDTH=70>" & "<B>Price</B>" & "</td>" & _
"</tr>"

  While Not objRS.EOF
    Response.Write _

  "<TR>" & _
    "<TD>" & objRS("Image") & "</TD>" & _
    "<TD ALIGN=CENTER>" & objRS("ProductID") & "</TD>" & _
    "<TD>" & objRS("Manufacturer") & "</TD>" & _
    "<TD>" & objRs("ProductDescription") & "</TD>" & _
    "<TD>" & objRS("ProductCode") & "</TD>" & _
    "<TD>" & FormatCurrency(objRS("UnitPrice")) & "</TD>" & _
  "</TR>" & _

    objRS.MoveNext
  Wend
Response.Write "</TABLE>"
  objRS.Close
  Set objRS = Nothing

End Select
%>

 
Old July 25th, 2003, 03:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Andy, I can't see anything obviously wrong except for a stray line-continuation character in your loop, but that doesn't explain why one Case works when the other doesn't.

I think it would help to figure out what's going wrong if you re-organise the code to make it much simpler. You are doing essentially the same thing in both Cases so try using this version instead and see if that helps.

Code:
<%
  varProductType = Request.Form("producttype")

  Dim strType, strStoredProcName
  
  Select Case varProductType

    Case "Mains Drills"

        strStoredProcName = "qryMainsDrills"
        strType = "Mains"

    Case "Cordless Drills"

        strStoredProcName = "qryCordlessDrills"
        strType = "Cordless"

    Case Else

        Response.Write("unrecognised product type")
        Response.End

  End Select

  Dim objRS, objComm, objParam 
  Set objComm = Server.Createobject("ADODB.Command")

  objComm.ActiveConnection = strConnect
  objComm.CommandText = strStoredProcName ' CHANGED
  objComm.CommandType = adCmdStoredProc

  Set objParam = _
     objComm.CreateParameter("Required Type", adVarChar, adParamInput, 50)
  objComm.Parameters.Append objParam

  objComm.Parameters("Required Type") = strType

  Set objRS = objComm.Execute

    Response.Write "<TABLE BORDER=""0"" CELLSPACING=""6"" CELLPADDING=""3"">" & _
    "<tr>" & _
    "<td WIDTH=80>&nbsp;</td>" & _
    "<td WIDTH=100><B>Product ID</B></td>" & _
    "<td WIDTH=120><B>Manufacturer</B></td>" & _
    "<td WIDTH=170><B>Description</B></td>" & _
    "<td WIDTH=150><B>Product Code</B></td>" & _
    "<td WIDTH=70><B>Price</B></td>" & _
    "</tr>"

  While Not objRS.EOF
    Response.Write _
    "<TR>" & _
      "<TD>" & objRS("Image") & "</TD>" & _
      "<TD ALIGN=CENTER>" & objRS("ProductID") & "</TD>" & _
      "<TD>" & objRS("Manufacturer") & "</TD>" & _
      "<TD>" & objRs("ProductDescription") & "</TD>" & _
      "<TD>" & objRS("ProductCode") & "</TD>" & _
      "<TD>" & FormatCurrency(objRS("UnitPrice")) & "</TD>" & _
    "</TR>"

    objRS.MoveNext
  Wend
  Response.Write "</TABLE>"
  objRS.Close
  Set objRS = Nothing
  Set objParam = Nothing
  Set objComm = Nothing

%>
hth
Phil
 
Old July 25th, 2003, 04:54 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Are both values passed through the Form collection in the same case? If I am not mistaken, the Case statement is Case sensitive....

Code:
Select Case varProductType

    Case "Cordless Drills"
    won't work when you pass "cordless drills"

HtH

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old July 25th, 2003, 05:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:
If I am not mistaken, the Case statement is Case sensitive....
The Case Else which I added will be trigerred if this is the problem.

In hindsight its better to change to
Code:
  Select Case UCase(varProductType)

    Case "MAINS DRILLS"

        strStoredProcName = "qryMainsDrills"
        strType = "Mains"

    Case "CORDLESS DRILLS"

        strStoredProcName = "qryCordlessDrills"
        strType = "Cordless"

    Case Else

        Response.Write("unrecognised product type")
        Response.End

  End Select
 
Old July 25th, 2003, 07:28 AM
Registered User
 
Join Date: Jul 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your help, I will try out your suggestions and post a reply to tell you if it works.
Thanks

 
Old July 25th, 2003, 07:52 AM
Registered User
 
Join Date: Jul 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar. In answer to your question, the answer is yes, no problems there.
pgtips. Tried your suggestion and everything is now OK.

Thanks for your help. Unlike me you must be a bit of a whizz on ASP, but I'm getting there.







Similar Threads
Thread Thread Starter Forum Replies Last Post
select case bacster Classic ASP Basics 2 May 1st, 2006 06:31 PM
Select Case junaidraja30 Access VBA 1 February 5th, 2005 08:30 PM
select case yuvalk SQL Server 2000 4 August 25th, 2004 02:33 PM
Select Case morpheus VB How-To 1 August 13th, 2003 09:14 AM
Select Case ziwez0 .NET Web Services 8 July 11th, 2003 02:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.