Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with combo box and database 1

Status
Not open for further replies.
gmagerr

gmagerr

Technical User
Aug 11, 2001
323
US
Hi guys i'm populating a dropdown list of customers from a table in my database. After a customer is selected, i'm going to another page that lists all the customers details. The problem i'm having is when the customer is selected and the form submitted, only the first customer in the table is displayed, not the actual customer that was selected. How do i pass the CustomerID (unique key) to the second page from a dropdown list??? here's the code.

===========================================================
First Page
===========================================================
<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<%
Option Explicit
Response.Buffer = True
Response.ContentType = &quot;text/html; charset=iso-8859-1&quot;
'------------------------------------------------------------------------------
'--- Module: .asp
'---
'--- Version: 1.0
'---
'--- Purpose:
'---
'--- Dependencies:
'---
'--- Notes:
'---
'--- Author: Gene Magerr genemagerr@hotmail.com
'--- Copyright 2001 Magerr Media all rights reserved
'------------------------------------------------------------------------------

'------------------------------------------------------------------------------
Dim cnCust, rsCust, strSQL, CustID
'------------------------------------------------------------------------------
'--- Declarations
'------------------------------------------------------------------------------
Set cnCust = Server.CreateObject(&quot;ADODB.Connection&quot;)
cnCust.Open &quot;DSN=Telmon&quot;

strSQL = &quot;SELECT * FROM Customers&quot;

Set rsCust = Server.CreateObject(&quot;ADODB.Recordset&quot;)
rsCust.Open strSQL, cnCust

'------------------------------------------------------------------------------
'--- Initialization
'------------------------------------------------------------------------------

'------------------------------------------------------------------------------
'--- Body
'------------------------------------------------------------------------------

'------------------------------------------------------------------------------
'--- Begin HTML output
'------------------------------------------------------------------------------
%>
<HTML>
<HEAD>
<TITLE>Untitled</TITLE>
</HEAD>

<BODY>
<DIV align=&quot;center&quot;>
<P> <% If Request.Querystring = &quot;NoCust&quot; Then %>
<B><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot;>You must select a customer to continue!</font></B>
<% End If %>
</P>
<P>Select a Customer from the list<BR>
or<BR>
<A href=&quot;AddCust.asp&quot;>Add a new Customer</A></P>
<P>
<% If rsCust.EOF <> False Then
Response.Write &quot;<table width='80%' align='center' cellspacing='0'><td align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'>There are currently no Customers<br>&quot;
Response.Write &quot;in the database</font></td></table><p>&quot;
Else
%>
</DIV>
<FORM name=&quot;form1&quot; method=&quot;post&quot; action=&quot;SelectJob.asp&quot;>
<DIV align=&quot;center&quot;>
<SELECT name=&quot;Customer&quot;>
<option selected>
<% Do While Not rsCust.EOF
Response.Write(&quot;<OPTION value=&quot;&quot;&quot; & rsCust(&quot;CustCompanyName&quot;) & &quot;&quot;&quot;>&quot; & rsCust(&quot;CustCompanyName&quot;) & &quot;</option>&quot;)
Session(&quot;CustID&quot;) = rsCust.Fields.Item(&quot;CustomerID&quot;).Value
'use MoveNext to fetch the next record
rsCust.MoveNext
Loop

%>
</select>
 
<INPUT type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;Submit&quot;>
<INPUT TYPE=&quot;hidden&quot; name=&quot;CustID&quot; value=&quot;<%=Session(&quot;CustID&quot;)%>&quot;>
</P>
<% End If %>
</DIV>
</FORM>
</BODY>
</HTML>
<%
'------------------------------------------------------------------------------
'--- End HTML Output
'------------------------------------------------------------------------------
rsCust.Close
Set rsCust = Nothing
cnCust.Close
Set cnCust = Nothing
'------------------------------------------------------------------------------
'--- All ASP post processing code goes here, as well as
'--- sub routines and functions
'------------------------------------------------------------------------------
%>

===========================================================
Second Page
===========================================================
<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<%
Option Explicit
Response.Buffer = True
Response.ContentType = &quot;text/html; charset=iso-8859-1&quot;
'------------------------------------------------------------------------------
'--- Module: .asp
'---
'--- Version: 1.0
'---
'--- Purpose:
'---
'--- Dependencies:
'---
'--- Notes:
'---
'--- Author: Gene Magerr genemagerr@hotmail.com
'--- Copyright 2001 Magerr Media all rights reserved
'------------------------------------------------------------------------------

'------------------------------------------------------------------------------
Dim cnCust, rsCust, strSQL, SiteID, Company, CustID
'------------------------------------------------------------------------------
'--- Declarations
'------------------------------------------------------------------------------

Response.Write Session(&quot;CustID&quot;)

Set cnCust = Server.CreateObject(&quot;ADODB.Connection&quot;)
cnCust.Open &quot;DSN=Telmon&quot;

strSQL = &quot;SELECT * FROM Customers WHERE CustomerID=&quot; & Session(&quot;CustID&quot;)

Set rsCust = Server.CreateObject(&quot;ADODB.Recordset&quot;)
rsCust.Open strSQL, cnCust
'------------------------------------------------------------------------------
'--- Initialization
'------------------------------------------------------------------------------

'------------------------------------------------------------------------------
'--- Body
'------------------------------------------------------------------------------

'------------------------------------------------------------------------------
'--- Begin HTML output
'------------------------------------------------------------------------------
%>
<HTML>
<HEAD>
<TITLE>Untitled</TITLE>
</HEAD>

<BODY>
<% If Request.Form(&quot;Customer&quot;) <> &quot;&quot; Then %>
<P> </P>
<TABLE width=&quot;75%&quot; border=&quot;1&quot; align=&quot;center&quot; cellspacing=&quot;0&quot; bordercolor=&quot;#006600&quot;>
<TR bgcolor=&quot;#CCFFCC&quot;>
<TD><DIV align=&quot;center&quot;>Name</DIV></TD>
<TD><DIV align=&quot;center&quot;>Address</DIV></TD>
<TD><DIV align=&quot;center&quot;>City </DIV></TD>
<TD><DIV align=&quot;center&quot;>State </DIV></TD>
<TD><DIV align=&quot;center&quot;>Zip</DIV></TD>
</TR>
<TR>
<TD height=&quot;23&quot;><DIV align=&quot;center&quot;><%=rsCust(&quot;CustCompanyName&quot;)%></DIV></TD>
<TD><DIV align=&quot;center&quot;><%=rsCust(&quot;CustAddress&quot;)%></DIV></TD>
<TD><DIV align=&quot;center&quot;><%=rsCust(&quot;CustCity&quot;)%>  </DIV></TD>
<TD><DIV align=&quot;center&quot;><%=rsCust(&quot;CustState&quot;)%></DIV></TD>
<TD><DIV align=&quot;center&quot;><%=rsCust(&quot;CustZip&quot;)%></DIV></TD>
</TR>
</TABLE>
<% Else
Response.Redirect &quot;SelectCust.asp?NoCust&quot;
End If %>
<P align=&quot;center&quot;>
<%
Session(&quot;Company&quot;) = rsCust.Fields.Item(&quot;CustCompanyName&quot;).Value
rsCust.Close
Set rsCust = Nothing

Dim rsSite, strSQLSite
Set rsSite = Server.CreateObject(&quot;ADODB.Recordset&quot;)
strSQLSite = &quot;SELECT * FROM JobSite WHERE CustomerID=&quot; & Session(&quot;CustID&quot;)
rsSite.Open strSQLSite, cnCust
%>
 Select JobSite for <%=Session(&quot;Company&quot;)%><BR>
or<BR>
<A href=&quot;AddJob.asp?CustID=<%=Session(&quot;CustID&quot;)%>&quot;>Enter
a new JobSite for</A> <%=Session(&quot;Company&quot;)%></P>
<FORM name=&quot;form1&quot; method=&quot;post&quot; action=&quot;ListJob.asp&quot;>
<DIV align=&quot;center&quot;>
<SELECT name=&quot;Jobsite&quot;>
<OPTION selected>
<% Do While Not rsSite.EOF
Response.Write(&quot;<OPTION value=&quot;&quot;&quot; & rsSite(&quot;SiteCompanyName&quot;) & &quot;&quot;&quot;>&quot; & rsSite(&quot;SiteCompanyName&quot;) & &quot;</option>&quot;)

Session(&quot;SiteID&quot;) = rsSite.Fields.Item(&quot;JobSiteID&quot;).Value

'use MoveNext to fetch the next record
rsSite.MoveNext
Loop
%>
</SELECT>
 
<INPUT type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;Submit&quot;>
<INPUT type=&quot;hidden&quot; name=&quot;SiteID&quot; value=&quot;<%=Session(&quot;SiteID&quot;)%>&quot;>
<INPUT type=&quot;hidden&quot; name=&quot;CustID&quot; value=&quot;<%=Session(&quot;CustID&quot;)%>&quot;>
</DIV>
</FORM>
<P align=&quot;center&quot;> </P>
<P align=&quot;center&quot;> </P>
</BODY>
</HTML>
<%
'------------------------------------------------------------------------------
'--- End HTML Output
'------------------------------------------------------------------------------

cnCust.Close
Set cnCust = Nothing
'------------------------------------------------------------------------------
'--- All ASP post processing code goes here, as well as
'--- sub routines and functions
'------------------------------------------------------------------------------
%>


I'm going to go to a third page as well, but i need to get the ID passed before i can continue. Thanks
 
Sort by date Sort by votes
Ok your problem is that you are specifying in the SQL to always get the customer info from the person that is currently set in the session variable. However this session variable is set inside your loop to populate your dropdown, meaning that the last entry into the dropdown will always be the one in the session variable. Instead of selecting from the session id, populate your options like so:
Code: 
 Response.Write(&quot;<OPTION value=&quot;&quot;&quot; & rsCust(&quot;CustomerID&quot;) & &quot;&quot;&quot;>&quot; & rsCust(&quot;CustCompanyName&quot;) & &quot;</option>&quot;)

Now we have a drop down with customer id's in the value of the options, on the second page instead of selecting where ID = your session variable, select where it is equal to that value:
Code: 
strSQL = &quot;SELECT * FROM Customers WHERE CustomerID=&quot; & Request.Form(&quot;Customer&quot;)

If this is way off, assume I misinterpreted something in your code and simplify the details, show exactly which drop down, a little db detail, and exactly which SQL stmt. I think I found the ones you were looking for, but I am far from perfect :)
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation
 
Upvote 0 Downvote
No prob (wink wink, nudge nudge, pitiful attempt at hinting to the link above)
:)
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation
 
Upvote 0 Downvote
hahaha right i just gave you a star DUH!! on my part... thanks again :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
393
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
411
JScannell
JScannell
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
555
leifoet
leifoet
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
354
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
544
xwb
xwb

Part and Inventory Search

Sponsor

Back
Top