drop down population help needed - thanks

K

Kingdom

I just wanted to add that you would not want to do it this way if your
table contained thousands of rows of data. You would be better off
using two dropdowns: one for the user to select a type, the other to
display the corresponding items based on the type selected.

With a few hundred records, however, both methods should work well.
Frankly, with an Access database, I would be leaning more towards
Tom's method.
Click to expand...

Couldn't wait till tomorrow, tried now but I must have messed up I'm
getting an error.

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/simple.asp, line 72 <========which is the end of the file?


This how it looks


<%
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("Database1_connectionstring")
%>

<%
dim rsTypes,rsComps, strSQL
Dim sCurCompType
'open the connection, then

Set rsTypes= server.createobject("adodb.recordset")
rsTypes.cursorlocation=adUseClient
strSQL= "SELECT DISTINCT Component_Type " & _
"FROM Parts_Table ORDER BY Component_Type; "
rsTypes.Open strSQL,cn,,,adCmdText
'disconnect the recordset
set rsTypes.ActiveConnection = nothing

Set rsComps= server.createobject("adodb.recordset")
rsComps.cursorlocation=adUseClient
strSQL= "SELECT UniqueID, Component_Type, Price, Component_Name " & _
"FROM Parts_Table ORDER BY Component_Type, Component_Name"
rsComps.Open strSQL,cn,,,adCmdText
'disconnect the recordset
set rsComps.ActiveConnection = nothing
cn.Close: set cn=nothing

'put the unique types into a recordset:
set rsTypes = rs.Clone
'disconnect the recordset
set rsTypes.ActiveConnection = nothing
'Put the components into another recordset and disconnect it
set rsComps = rs.NextRecordset
set rsComps.ActiveConnection = nothing

'clean up the initial recordset
rs.Close: set rs=nothing
cn.Close: set cn=nothing

'Create the dropdowns:
do until rsTypes.EOF
sCurCompType = rsTypes(0).Value
Response.Write "Component Type " & sCurCompType & ": "
Response.Write "<select name=""" & sCurCompType & """>"
rsComps.Filter="Component_Type='" & sCurCompType & "'"
do until rsComps.EOF
Response.Write "<option value-""" & rsComps(0).Value
Response.Write """>" & rsComps(2).Value
Response.Write "</option>"
rsComps.MoveNext
loop
Response.Write "</select><BR>"
rsTypes.MoveNext
loop
rsTypes.close:set rsTypes=nothing
rsComps.close:set rsComps=nothing
%>
</body>
</html>
 
K

Kingdom

To be clear. You have a table called Parts_Table with the fields -
Component_Type, Price and a field for the name of the part, correct?

What I'd suggest you do, is to query the ENTIRE table, sorted by the
Component_Type then the Component_Name.

Set objRS = objDC.Execute("SELECT UniqueID, Component_Type, Price,
Component_Name FROM Parts_Table ORDER BY Component_Type,
Component_Name")

This will give you a recordset with all of the items in your table
sorted by component type. I'd iterate through the list and when you
hit a new Component_Type, start a new select box.

Dim sCurrentComponentType
Set objRS = 'As above
if not objRS.EOF then
sCurrentComponentType=objRS.Fields("Component_Type").Valu
e
Response.Write "<select name=""" &
sCurrentComponentType &
""">"
Do While not objRS.EOF
if objRS.Fields("Component_Type").Value <>
sCurrentComponentType
then
sCurrentComponentType=objRS.Fields("Component_Type").Va
lue Response.Write "</select>"
Response.Write "<select name=""" &
sCurrentComponentType &
""">"
end if
'Now the individual options

Response.Write "<option value=""" &
objRS.Fields("UniqueID").Value & """>"
objRS.MoveNext
Loop
Response.Write "</select>"
end if
Set objRS=nothing
Click to expand...

Sorry to be such a bother but I cannot get the script to run and cannot
fathom why, Im getting getting:-

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/tom.asp, line 69, column 66
Set objRS = objDC.Execute("SELECT UniqueID, Component_Type, Price,

--
We are all in the gutter, but some of us are looking at the stars.

=========================================================================
Walrus Home alt.binaries.pictures.wallpaper <=vote here every weekend.
FAQ found at http://members.rogers.com/heretic54/
(The most up to date version is posted in alt.binaries.pictures.wallpaper
on Mondays and Thursdays. PLEASE READ BEFORE POSTING)
For Contest Archives, Artists & weekly entries
http://www.weeklywalrus.com
 
B

Bob Barrows

Kingdom said:
Sorry to be such a bother but I cannot get the script to run and
cannot fathom why, Im getting getting:-

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/tom.asp, line 69, column 66
Set objRS = objDC.Execute("SELECT UniqueID, Component_Type, Price,
Click to expand...

You need to put the entire SQL statement on one line.

Actually, I highly recommend assigning the statement to a variable, so you
can response.write it for debugging purposes:

dim sSQL
'***all one line ***********************************************************
sSQL = "SELECT UniqueID, Component_Type, Price, Component_Name FROM
Parts_Table ORDER BY Component_Type, Component_Name"
'***all one line ***********************************************************
Response.write sSQL
Set objRS = objDC.Execute(sSQL,,1)

Another way to do this is to use line continuation characters:
sSQL = "SELECT UniqueID, Component_Type, Price, " & _
"Component_Name FROM Parts_Table ORDER BY " & _
"Component_Type, Component_Name"

HTH,
Bob Barrows
 
B

Bob Barrows

Kingdom said:
Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are
in conflict with one another.
/simple.asp, line 72 <========which is the end of the file?
Click to expand...
This is caused by the failure to define the ADO constants. Here is the best
way to do that:
http://www.aspfaq.com/show.asp?id=2112

HTH,
Bob Barrows
 
T

Tom B

Yes, bad me. I should have used a variable.

Bob Barrows said:
You need to put the entire SQL statement on one line.

Actually, I highly recommend assigning the statement to a variable, so you
can response.write it for debugging purposes:

dim sSQL
'***all one line ***********************************************************
sSQL = "SELECT UniqueID, Component_Type, Price, Component_Name FROM
Parts_Table ORDER BY Component_Type, Component_Name"
'***all one line ***********************************************************
Response.write sSQL
Set objRS = objDC.Execute(sSQL,,1)

Another way to do this is to use line continuation characters:
sSQL = "SELECT UniqueID, Component_Type, Price, " & _
"Component_Name FROM Parts_Table ORDER BY " & _
"Component_Type, Component_Name"

HTH,
Bob Barrows
Click to expand...
 
K

Kingdom

Sill getting an error Tom, line 94 is end of the script, I just don't
have enough understanding yet to efectivly debug scripts but its probably
very obvious to you where I've messed up.

Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/tom.asp, line 94
then


<%
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("Database1_connectionstring")
%>
<%
'To be clear. You have a table called Parts_Table with the fields -
'Component_Type, Price and a field for the name of the part, correct?

'What I'd suggest you do, is to query the ENTIRE table, sorted by the
'Component_Type then the Component_Name.

Set objRS = objDC.Execute("SELECT UniqueID, Component_Type, Price,
Component_Name FROM Parts_Table ORDER BY Component_Type, Component_Name")

Dim sCurrentComponentType
sSQL = "SELECT UniqueID, Component_Type, Price, Component_Name FROM
Parts_Table ORDER BY Component_Type, Component_Name"

then
sCurrentComponentType=objRS.Fields("Component_Type").Value
Response.Write "<select name=""" & sCurrentComponentType
& """>"
Do While not objRS.EOF
if objRS.Fields("Component_Type").Value <>
sCurrentComponentType
then
sCurrentComponentType=objRS.Fields
("Component_Type").Value
Response.Write "</select>"
Response.Write "<select name=""" & sCurrentComponentType
& """>"
end if
'Now the individual options

Response.Write "<option value=""" &
objRS.Fields("UniqueID").Value & """>"
objRS.MoveNext
Loop
Response.Write "</select>"
end if
Set objRS=nothing
%><p></p>
 
T

Tom B

Could you show the whole page?

I'm guessing that the word wrap is the problem. In VBScript, the end of the
line is a carriage return so this...

if x=1
then

is wrong and should be
if x=1 then


so in the code below....

if objRS.Fields("Component_Type").Value <> sCurrentComponentType then

needs to be all on one line.
 
B

Bob Barrows

You have to be very careful when using code from a newsgroup message. News
readers tend to put line breaks where the message composers never intended
there to be line breaks. That is why when I post code, I always try to use
line continuation characters when it looks like a line may go beyond 70
characters.

Looking at the code you've posted, let's take this section as an example:

Response.Write "<option value=""" &
objRS.Fields("UniqueID").Value & """>"

In the newsgroup post, it is showing up on two lines. It should be a single
line. The problem is: I cannot tell whether the code in your asp page
actually looks like this (in which case an error will result), or if the
line break was inserted by your news reader, making it show up as two lines
in the post.

You will need to look at the code and learn to recognize where the
inadvertant line breaks were inserted. One clue is that a line should never
end with &. Another clue is the indenting. The beginning of the above line
is indented. The part where the line break was inserted is not indented.
This is a strong clue that the text was originally written as a single line.

--
HTH,
Bob Barrows - ASP MVP
Please reply to the newsgroup. The email account listed in my From header is
my spam trap, so I don't check it very often. You will get a quicker
response by posting to the newsgroup.
 
K

Kingdom

Trying to produce a page that works something like this

http://www.pc-doctors.com/frame.aspx

Sorted the "then" but now getting:-

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/tom.asp, line 86, column 121
sSQL = "SELECT UniqueID, Component_Type, Price, Component_Name FROM
Parts_Table ORDER BY Component_Type, Component_Name" then
---------------------------------------------------------------------------
---------------------------------------------^


Full page - called tom.asp using frontpage 2002 running ISS localy

<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows 1252">
<title>New Page 1</title>

<meta name="Microsoft Theme" content="tp-or12 1110">
<meta name="Microsoft Border" content="tlb, default">
</head>

<body>
<%
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("Database1_connectionstring")
%>
<%
'To be clear. You have a table called Parts_Table with the fields -
'Component_Type, Price and a field for the name of the part, correct?

'What I'd suggest you do, is to query the ENTIRE table, sorted by the
'Component_Type then the Component_Name.

Set objRS = objDC.Execute("SELECT UniqueID, Component_Type, Price,
Component_Name FROM Parts_Table ORDER BY Component_Type, Component_Name")

Dim sCurrentComponentType
sSQL = "SELECT UniqueID, Component_Type, Price, Component_Name FROM
Parts_Table ORDER BY Component_Type, Component_Name" then
sCurrentComponentType=objRS.Fields("Component_Type").Value
Response.Write "<select name=""" & sCurrentComponentType &
""">"
Do While not objRS.EOF
if objRS.Fields("Component_Type").Value <>
sCurrentComponentType then
sCurrentComponentType=objRS.Fields("Component_Type").Value
Response.Write "</select>"
Response.Write "<select name=""" & sCurrentComponentType &
""">"
end if
'Now the individual options

Response.Write "<option value=""" &
objRS.Fields("UniqueID").Value & """>"
objRS.MoveNext
Loop
Response.Write "</select>"
end if
Set objRS=nothing
%><p></p>

</td></tr></table></body></html>
 
K

Kingdom

Have posted a screen snap jpg of code to alt.binaties.misc with a heading
of :-

For Tom & Bob file is called tomscode
 
T

Tom B

the "then" is part of an if..then statement, as in

if somecondition then
do this
end if

so your assignment statement
sSQL="blahblah"
shouldn't have a "then" at the end of it
 
R

Ray at

Tom, is that your real e-mail address? I'd be surprised if so. Please
e-mail me privately at ray at securitynat · com.

Thanks,

Ray at work
 
K

Kingdom

I don't have access to that group.
Click to expand...


Hi posted current code to alt.test called it "For Tom" its a 12k gif file
this time.

The link I posted didnt show the page I wanted you to see for an idea of the
type of page I'm trying to create

http://www.pc-doctors.com select desktop from the system button then
mainstream from the middle of the page then customize and price.
 
K

Kingdom

I've attached a zip with a little sample in it.
Click to expand...

Thankyou Tom I appreciate you taking the time to do this for me and your
going to thinik I'm crazy because I cannot seem to connect to the DB.
It's probably very obvious what I'm doing wrong but I just don't know
what to do next.

I had been using:-
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("Database1_connectionstring")

which worked ok but don't iknow much about the OLEDB connection

The sConnectionString is all on one line

Thanks again

script
===========
<%

'Dim sConnectionString
'sConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" &amp; "Data
Source=\fpdb\kelvindb.mdb;"" "User Id=admin;" &amp; "Password=;"

%>
<%

'FOR DEBUGGING
Dim field
for each field in Request.Form
Response.Write "<li>" & field & ": " & Request.Form
(field) & "</li>"
next
'END DEBUGGING
%>
<form method=Post action=SelectBoxes.asp>
<% call GetSelectBoxes%>
<br>
<input type=submit value=Go>
</form>

</BODY>
</HTML>
<%

Sub GetSelectBoxes()

Dim oCN
Dim oRS
Dim sSQL

sSQL="SELECT UniqueID, Component_Type, Price, Component_Name
FROM Parts_Table ORDER BY Component_Type, Component_Name"
Set oCN=CreateObject("ADODB.Connection")
oCN.Open sConnectionString
Set oRS=oCN.Execute(sSQL)
if not oRS.EOF then
Dim sCurrentBox
Dim bIsFirst
bIsFirst=true

Do While not oRS.EOF
'OK, we have our recordset.
'Let's go through each "thingy" and create a box for
it.
if sCurrentBox <> oRS.Fields("Component_Type") then

'it's time for a new select box.
'but first, let's close the last box.
if bIsFirst=false then
'make sure it's not the first select box.
Response.Write "</select><br>" & vbCrLf
else
bIsFirst=false
end if

'OK, create the new box.
sCurrentBox=oRS.Fields("Component_Type")
Response.Write sCurrentBox & ": <select
name=""" & sCurrentBox & """>" & vbCrLf
end if
'Now add the individual item
Response.Write vbTab & "<option value=""" &
oRS.Fields("UniqueID") & """>" & _
trim(oRS.Fields
("Component_Name")) & _
"</option>" & vbCrLf


oRS.MoveNext
Loop

'Let's close that last one
Response.Write "</select>" & vbCrLf

end if
Set oRS=nothing
oCN.Close
Set oCN=nothing
End Sub

%>
 
B

Bob Barrows

Kingdom said:
I cannot seem to connect to
the DB. It's probably very obvious what I'm doing wrong but I just
don't know what to do next.

I had been using:-
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("Database1_connectionstring")

which worked ok but don't iknow much about the OLEDB connection

The sConnectionString is all on one line

Thanks again

script
===========
<%

'Dim sConnectionString
'sConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" &amp; "Data
Source=\fpdb\kelvindb.mdb;"" "User Id=admin;" &amp; "Password=;"
Click to expand...

You need to supply the full physical path to the database. You can use
Server.MapPath. Like this:
'sConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("\fpdb\kelvindb.mdb")

Response.Write sConnectionString


You do not need to supply the user id and password attributes with an Access
database, unless you are using workgroup security.

HTH,
Bob Barrows
 
T

Tom B

Those quotes aren't really there are they?
sConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\fpdb\kelvindb.mdb;User Id=admin;Password='';"

Your Data Source needs to point to the Full path and file, so either hard
code...c:\fpdb\kelvindb.mdb or use the Server.MapPath which does it for
you....Data Source=" & Server.MapPath("\fpdb\kelvindb.mdb") & "; User
Id=admin;Password='';"

Your other choice is to use
sConnectionString=Application("Database1_connectionstring")
 
K

Kingdom

Those quotes aren't really there are they?
sConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\fpdb\kelvindb.mdb;User Id=admin;Password='';"

Your Data Source needs to point to the Full path and file, so either hard
code...c:\fpdb\kelvindb.mdb or use the Server.MapPath which does it for
you....Data Source=" & Server.MapPath("\fpdb\kelvindb.mdb") & "; User
Id=admin;Password='';"

Your other choice is to use
sConnectionString=Application("Database1_connectionstring")
Click to expand...

Thanks Tom I think it must be connecting now using

<%
Dim sConnectionString
sConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
Server.MapPath("\fpdb\kelvindb.mdb")

%>

but I'm getting ?

Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
/Selectboxes.asp, line 138

I feel so helpless when it throws up errors!
 
T

Tom B

What's on line 138?

Kingdom said:
Thanks Tom I think it must be connecting now using

<%
Dim sConnectionString
sConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
Server.MapPath("\fpdb\kelvindb.mdb")

%>

but I'm getting ?

Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
/Selectboxes.asp, line 138

I feel so helpless when it throws up errors!
Click to expand...
 
T

Tom B

What's on line 138?

Is it an update or an insert? If so, then you are missing a required
parameter.

If you do an update(or an insert) you have to either a) provide a value for
all fields or b) specify the fields you are providing a value for

a) INSERT INTO Parts_Table Values ('MyPartType', 'MyPartName')
b) INSERT INTO Parts_Table(Part_Type, Part_Name)
VALUES('MyPartType','MyPartName')
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Urgent Help Needed: Supporting Education and Family Through Hard Work 0
Drop down list/menu 3
drop down question 2
JavaScript population of ASP drop down 0
drop down 1
Cascading drop-down restructuring 12
Add value to third drop down list 2
Populate drop down list 1

Members online

Total: 353 (members: 4, guests: 349)
Robots: 121

Forum statistics

Threads
474,132
Messages
2,570,775
Members
47,333
Latest member
DongPouncy

Latest Threads

Top