...

View Full Version : Creating dropdown from database



BabbleFish
06-19-2007, 05:00 PM
Iím attempting to create an online form that allows users to update records in a MS ACCESS 2000 database. I have no problem showing a list of records available for update and generating the html form with information from the database (i.e. date recorded entered, record type, etc.) My problem is that I canít figure out how to create dependent drop down lists. I want the options in the drop down list to be populated by another table in the same database but the options listed are dependent upon a field in the form that is autopopulate by the record to be updated.

Example:
User selects record A to update. Portions of record A populate in Form1. The are additional (not populated from record A that need to be added to record A) fields in form1 that require user input. I want these fields to be drive by options in table B in the database. The list of options that populate are also dependent on todayís date as well as another field that populates from record A.

I can do this in an ACCESS Form but finding it difficult to converting it to a web solution.

Any help you can provide would be greatly appreciated .

Code:

<%@ Language="VBScript" %>
<% Option Explicit %>


<html>
<head><title>Record Updates</title>
</head>
<body>

<%
'variable
Dim SQL, connection, recordset
Dim sConnString, ID

'ID passed from record selection screen
ID=request.querystring("ID")


SQL = "SELECT * FROM _RCAT_DATA WHERE ID=" & ID

Set Connection=Server.CreateObject("ADODB.Connection")
Set Recordset=Server.CreateObject("ADODB.Recordset")

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("NEW R-CAT.mdb")

Connection.Open(sConnString)

recordset.Open SQL,connection
%>

<div align='center'>
<table width="500" border="0" cellspacing="0" cellpadding="0" align="center">
<tr align="left" valign="top">
<td>
<div align="center">
<p>RCAT Audit Form</p>
<!-- start the html form -->
<form method="post" action="update_record.asp" name="form">
<input type="hidden" name="id" value="<%= recordset("id")%>">
<table width="100%" border="0">
<tr>
<td width="45%">Observer:&nbsp;
</td>
<td colspan="2">
<input type="text" name="Observer" id="req1" value="<%= recordset("Observer")%>" size='30'>
</td>
<td>
Date:&nbsp;
</td>
<td>
<input type="text" name="audit_date" id="req1" value="<%= recordset("audit_date")%>">
</td>
<td width="46">
SessionID:
</td>
<td width="148">
<input type="text" name="SESSION_ID" id="req1" value="<%= recordset("SESSION_ID")%>">
</td>
</tr>
</table>
<table width="100%">
<tr>
<td colspan="5">Call Category - Type - Reason</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td width="148">RCAT</td>
</tr>
<tr>
<td colspan="5">
<select name="RCAT_code" id="req1">
<option>Account Info-Change-Feature/Service</option>
</select>
</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td width="148">
<input type="text" name="Ob_Type" id="req1" value="<%= recordset("Ob_Type")%>">
</td>
</tr>
</table>
<tr>
<td width="45%">Story Date:
&nbsp;&nbsp;&nbsp;
</td>
<td colspan="2">

</td>
</tr>
<td colspan="3">
<div align="center">
<input type="submit" name="Submit" value="Submit">
</div>
</td>
</tr>
<tr>
<td colspan="3">&nbsp;</td>
</tr>
</table>
<br>
<br>
</form>
<!-- end the html form -->
</div>
</td>
</tr>
</table>
</div>
<%
'close the connection and recordset objects
recordset.Close
Set recordset=Nothing
connection.Close
Set connection=Nothing
%>


</body>
</html>

miranda
06-19-2007, 11:48 PM
Just loop through whichever database has the values

I am only showing the <select> tag here you can get the rest



<select name="RCAT_code" id="req1">
<% Do Until recordset.EOF %>
<option value="<%=recordset("")%>"><%=recordset("")%></option>
<%recordset.MoveNext
Loop
%>
</select>

BabbleFish
06-20-2007, 12:05 AM
Miranda - Thank you for your response!!

The code your provided has helped me generate a drop down list based on data from the table referenced in my SQL statement.

What I am trying to do is generate the drop down list from another table in the database and I'm not sure how to handle the ADO connection/SQL statement. I've tried stringing SQL statements together but it seems that Access does not appreciate this format and returns the following error.

Microsoft JET Database Engine error '80040e14'
Characters found after end of SQL statement.

Additionally, is there a way to create dependant list that is dependent on information entered in an html form. I'm trying to convert the following SQL into something that will work in a website.

SELECT RCAT_CALL_REASONS.RCAT_code, RCAT_CALL_REASONS.definition FROM RCAT_CALL_REASONS WHERE (((RCAT_CALL_REASONS.end_date)>FORMS.RCAT_FORM.OBSVTN_DATE) And ((RCAT_CALL_REASONS.start_date)<FORMS.RCAT_FORM.OBSVTN_DATE) And ((RCAT_CALL_REASONS.TechSupport_Specific)=IIf(FORMS.RCAT_FORM.LOB="Technical Services",-1,0) Or (RCAT_CALL_REASONS.TechSupport_Specific)=IIf(FORMS.RCAT_FORM.LOB="Technical Services",0,0))) ORDER BY RCAT_CALL_REASONS.RCAT_code;

Thanks again for your help!!!

miranda
06-20-2007, 12:23 AM
The easiest way for a beginner is to use a 2nd recordset object. If this table is in the same database you can use the same connection.
So Do something like this



<select name="RCAT_code" id="req1">
<%
Dim oRs
Set oRs = Server.CreateObject("ADODB.Recordset")
SQL = "Put The Query Used To Fill The Drop Down Here"
oRs.Open SQL, Connection
Do Until oRs.EOF %>
<option value="<%=oRs("")%>"><%=oRs("")%></option>
<%oRs.MoveNext
Loop
'Always do the Garbage Handling as ASP is poor at it
oRs.Close
Set oRS = Nothing
%>
</select>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum