Wrox Programmer Forums
| 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 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 January 4th, 2006, 10:51 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data Retrival

I have a problem here. I have a database with 3 tables in it.(Imagine as there are 3 levels, one level for each table)

on level 1, there are 3 entries:A,B,C

on level 2, the entries are tied to one of the entries in level 1, ie those in level 2 will be A.1, A.2, A.3, B.1,B.2,C.1,C.2.....

on level 3, the entries are tied to one of the entries in level 2, ie those in level 3 will be A.1.1, A.1.2, A.1.3, A.2.1,A.2.2,B.1.2,C.2.1.....

I have 3 combo boxes on my webpage. I want it to be able to do the following:

when i make my choice on combobox 1 which holds the information of level 1, only those entries corresponding to the choice made in combobox1. ie if in combobox 1, i chose A, then i only wan those of A.1,A.2,A.3 to be shown in combobox 2.... after which, say I choose A.1 in combobox 2, I only wan those of A.1.1, A.1.2,A.1.3 to be shown in combobox 3.

Is this achievable in asp and sql since it sort of requires dynamic programming?

thanks for any inputs.


 
Old January 4th, 2006, 11:13 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. I would place an onChange event inside each combo box that you want the slection to alter the values inside another. This event will reload the page and retrieve info for any child combobox based on the users selection.

Eg for combobox 1:

------------mostly cut n paste code just finish the sql----


<% dim getInfo
sql = "SELECT id,name FROM....;"
set getInfo = conn.execute(sql) %>
<% IF not getInfo.EoF Then %>
     <SELECT NAME='comboOne' SIZE='1' onChange="updateForm.submit();">
<% DO UNTIL getInfo.EOF %>
         <OPTION VALUE='<%= getInfo(0) %>'><%= trim(getInfo(1)) %></OPTION>
<% getInfo.MoveNext
     LOOP
     set getInfo = nothing
ELSE %>
     <i>No Data Found</i>
<% END IF%>
     </SELECT>

Then for combobox 2 you need to do something like:

<% if trim(request.form("comboOne")) <> "" then
     'form has been submitted so get limited data
     sql = "SELECT id,name FROM....;"
else
     'form has not been submitted (default page load)
     sql = "SELECT id,name FROM....;"
end if
set getInfo = conn.execute(sql) %>
<% IF not getInfo.EoF Then %>
     <SELECT NAME='comboTwo' SIZE='1' onChange="updateForm.submit();">
<% DO UNTIL getInfo.EOF %>
         <OPTION VALUE='<%= getInfo(0) %>'><%= trim(getInfo(1)) %></OPTION>
<% getInfo.MoveNext
     LOOP
     set getInfo = nothing
ELSE %>
     <i>No Data Found</i>
<% END IF%>
     </SELECT>


---------------------------------

Wind is your friend
Matt
 
Old January 4th, 2006, 11:20 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

Left one thing out. Replace line 7 of my code with:

<OPTION VALUE='<%= getInfo(0) %>' <% IF Trim(request.form("comboOne")) = getInfo(0) THEN Response.Write " SELECTED " END IF %>><%= trim(getInfo(1)) %></OPTION>

This will ensure the users selection remains selected in comboOne when the page reloads (the onChange event is fired)

Wind is your friend
Matt
 
Old January 5th, 2006, 12:10 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi i tried the code you provided and amended it as follows:

Code:
<% dim getInfo 
 sql = "SELECT RCS_L1, RCS_L1_desc FROM RCSReqClassL1"
 set getInfo = conn.execute(sql) %>
<% IF not getInfo.EoF Then %>
<SELECT NAME='comboOne' SIZE='1' onChange="updateForm.submit();">
<%    DO UNTIL getInfo.EOF %>
<OPTION VALUE='<%= getInfo(0) %>' <% IF  Trim(request.form("comboOne")) = getInfo(0)   THEN Response.Write " SELECTED " END IF %>><%= trim(getInfo(1)) %></OPTION>     
<%       getInfo.MoveNext
      LOOP
      set getInfo = nothing
   ELSE %>
      <i>No Data Found</i>
<% END IF%>
</SELECT>


-for combobox 2

<% if trim(request.form("comboOne")) <> "" then
    'form has been submitted so get limited data
    sql = "SELECT RCS_L2, RCS_L2_desc FROM RCSReqClassL2"
   else
    'form has not been submitted (default page load)
    sql = "SELECT RCS_L2, RCS_L2_desc FROM RCSReqClassL2"
   end if
   set getInfo = conn.execute(sql) %>
<% IF not getInfo.EoF Then %>
     <SELECT NAME='comboTwo' SIZE='1' onChange="updateForm.submit);">
<%    DO UNTIL getInfo.EOF %>
   <OPTION VALUE='<%= getInfo(0) %>'><%= trim(getInfo(1)) %></OPTION>
<%       getInfo.MoveNext
      LOOP
     set getInfo = nothing
  ELSE %>
     <i>No Data Found</i>
<% END IF%>
</SELECT>
but i got this error

  Microsoft VBScript runtime error '800a01a8'

Object required: ''

where did i go wrong?

 
Old January 5th, 2006, 12:43 AM
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

This is an ambiguous, unhelpful error. To help others help you, you need to:

1..Turn off friendly errors in the advanced settings of your browser
2..Run the page. Now the error will tell where (line number) the problem is
3..Point out this line in your code.

FYI include semi colons inside the string trailing your statements:
sql = "SELECT RCS_L2, RCS_L2_desc FROM RCSReqClassL2;"

I will be going out soon. This method will work...




Wind is your friend
Matt
 
Old January 5th, 2006, 01:23 AM
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

Ok Im out for now. The whole idea of this condition
was to run differeent statements:

<% if trim(request.form("comboOne")) <> "" then
    'form has been submitted so get limited data
    sql = "SELECT RCS_L2, RCS_L2_desc FROM RCSReqClassL2"
   else
    'form has not been submitted (default page load)
    sql = "SELECT RCS_L2, RCS_L2_desc FROM RCSReqClassL2"
   end if

Even if you have empty record sets my code should print
 'no data found' It looks error free to me. Do you have
a connection etc...

Wind is your friend
Matt
 
Old January 5th, 2006, 03:28 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

my connection is done by the following:

Code:
<%    
    dim strCnn
    dim Cnn

    'Establish a connection to the database
    set cnn = server.createobject ("ADODB.Connection")
    strCnn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & Server.MapPath("Radar.mdb") & ";Uid=Admin;Pwd=;Mode=3;"
    Cnn.open strCnn

function GetRecordset(strSQL,arrResults)
    dim rs
    set rs = server.CreateObject("ADODB.Recordset")
    rs.Open strSQL, Cnn
    if not rs.EOF then
        arrResults = rs.GetRows()
        GetRecordset = ubound(arrResults,2)+1
    else
        GetRecordset = 0
    end if
    rs.Close
end function

%>
anyway how would you suggest that I have the 2 different SQl statements for :

<% if trim(request.form("comboOne")) <> "" then
    'form has been submitted so get limited data
    sql = "SELECT RCS_L2, RCS_L2_desc FROM RCSReqClassL2"
   else
    'form has not been submitted (default page load)
    sql = "SELECT RCS_L2, RCS_L2_desc FROM RCSReqClassL2"
   end if

I'm totally clueless about this issue. will really appreciate any help. thanks a million


 
Old January 5th, 2006, 07:29 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

Did you read this post?

------------------------------start--
This is an ambiguous, unhelpful error. To help others help you, you need to:

1..Turn off friendly errors in the advanced settings of your browser
2..Run the page. Now the error will tell where (line number) the problem is
3..Point out this line in your code.

-------------------------finish--

;;;anyway how would you suggest that I have the 2 different SQl statements for...
I would suggest you read your own first post, thats why. You want your second
checkbox to show content based on the users selection of the first. You need to
get the contents from the DB therefore you need to run different sql on page load
compared to when the onChange event fires (which reloads the page and RE POPULATES
the second checkbox)

sorry mate im not sure how to explain it any different.



Wind is your friend
Matt
 
Old January 9th, 2006, 11:42 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

so sorry, i have been trying to figure things out but to no avail

Code:
<% dim getInfo 
 sql = "SELECT RCS_L1, RCS_L1_desc FROM RCSReqClassL1"
 set getInfo = conn.execute(sql) %>
<% IF not getInfo.EoF Then %>
<SELECT NAME='comboOne' SIZE='1' onChange="updateForm.submit();">
<%    DO UNTIL getInfo.EOF %>
<OPTION VALUE='<%= getInfo(0) %>' <% IF  Trim(request.form("comboOne")) = getInfo(0)   THEN Response.Write " SELECTED " END IF %>><%= trim(getInfo(1)) %></OPTION>     
<%       getInfo.MoveNext
      LOOP
      set getInfo = nothing
   ELSE %>
      <i>No Data Found</i>
<% END IF%>
</SELECT>
the line in bold is the error. This is the error message: "Microsoft VBScript runtime error '800a01a8'

Object required: ''

"


in addition,
 
Quote:
quote:
Quote:
I would suggest you read your own first post, thats why. You want your second
checkbox to show content based on the users selection of the first. You need to
get the contents from the DB therefore you need to run different sql on page load
compared to when the onChange event fires
(which reloads the page and RE POPULATES
the second checkbox)
on page load, i can just load all the data from the table, but OnChange, i only want those which corresponds to that of the choice made in combobox 1.

any advice pls?

 
Old January 10th, 2006, 07:18 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

Ok. Your method of connecting and executing a query is different from the way I do things.

BTW : There are many ways to skin, niether of us are rite or wrong. You are getting the erorr
because you are connecting my way using your connection/execution method. It will never work like this.

IMO you should look at your 'connection string', how its 'executed' and 'called' and understand

Anyhow this will eliminate your erorr (use my method):

<%
dim cString,conn,getInfo
'This is myn minus credentials
'cString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\xxx.mdb;User Id=xxx;Password=; "
'this is yours:
cString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & Server.MapPath("Radar.mdb") & ";Uid=Admin;Pwd=;Mode=3;"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open(cString)

'now you can use my method of execution which is:
'run sql
sql = "Select.....;"
'execute
set getInfo = conn.execute(sql)
'now use your recordset
%>


;;;on page load, i can just load all the data from the table, but OnChange, i only want those which corresponds to that of the choice made in combobox 1.
Yes I understand this, thats wy you have the following if condition:


Then for combobox 2 you need to do something like:

<% if trim(request.form("comboOne")) <> "" then
      'form has been submitted so get limited data
      sql = "SELECT id,name FROM....;"
   else
      'form has not been submitted (default page load)
      sql = "SELECT id,name FROM....;"
   end if
   set getInfo = conn.execute(sql) %>




Wind is your friend
Matt




Similar Threads
Thread Thread Starter Forum Replies Last Post
asp 3.0 classic&access *formula retrival)? saad_rashad Classic ASP Databases 3 February 18th, 2007 03:47 PM
Data Retrival xbenx Classic ASP Professional 0 January 4th, 2006 10:54 PM
Retrival of Server Side DateTime with ACCESS dubey.kuldeep Access 2 November 1st, 2005 12:37 PM
retrival of decrypted data msrnivas General .NET 0 December 7th, 2004 02:52 AM





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