Hi All,
I'm relatively new to ASP and databases. I am trying to create a Select dropdown list dynamically from my Access Database. A user would select a site from that dropdown to see all the customer complaints for that site.
I have 2 problems.
1. The SELECT list only pulls 2 of the sites from my table. This is a field that is pulling from another table called 'sites'.
2. When I click on the submit button for one of the sites, it runs into the same error over and over again. This is the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'site=Chinn Center'.
/database/defaultselect.asp, line 40
Here is my (admittedly) sloppy code from beginning to end. I am trying to get it to use a GET to have the results pulled into the same page:
-------------BEGIN CODE--------------------------------
<HTML>
<HEAD>
<TITLE>Testing The Customer Complaint Database</TITLE>
</HEAD>
<BODY BGCOLOR="ffffff" text="black">
<%
'Dimension Variables
Dim adoCon 'Holds the Database Connection Object
Dim rsCustomerComplaint 'Holds the recordset pulled from the records in the database
Dim strSQL 'Holds the SQL Query to Query the Database
Dim lngRecordNo 'Holds the record number to be updated in Long Integer format
'Create an ADO Connection Object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-Less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("customercomplaint.mdb") & ";"
'Create an ADO recordset object that holds the records from the DBASE
Set rsCustomerComplaint = Server.CreateObject("ADODB.Recordset")
'Initialize the strSQL variable with a SQL statement to query the DBASE
strSQL = "SELECT * FROM customercomplaint"
if request.querystring("site") <> "" Then
strSQL = strSQL & " WHERE site=" & Replace(Request.QueryString("site"), "'", """")
End If
strSQL = strSQL & " ORDER BY site"
'Open the recordset with the SQL Query returning results of query to the recordset
rsCustomerComplaint.Open strSQL, adoCon
%>
<form action=defaultselect.asp method=GET>
<SELECT NAME=site>
<option selected>Please Select a Site</option>
<%
DO While Not rsCustomerComplaint.EOF
%>
<option value="<%= rsCustomerComplaint.Fields("site") %>"><%= rsCustomerComplaint.Fields("site") %></option>
<%
rsCustomerComplaint.MoveNext
Loop
%>
</Select>
<input type=submit name=submit value=search>
</form>
<%
If Request.QueryString("site") <>"" Then
Response.Write ("<BR><BR><BR>")
'Get the ID number that was passed from the update_complaint.asp page. We need to also convert it to a Long Integer data type. Do this by using the 'CLng' VBSCript function. We get the ID Number by using the 'QueryString' method of the ASP 'Request' object
lngRecordNo = CLng(Request.QueryString("ID_no"))
'Loop through the Recordset using a Do While loop
Do While not rsCustomerComplaint.EOF
'Write the HTML to display the contents of the recordset
Response.Write ("<a href=update_complaint.asp?ID_no=" & rsCustomerComplaint("ID_no") &">Update This Complaint</a>")
Response.Write ("<br><div>")
Response.Write ("Date Complaint Recorded: " & rsCustomerComplaint("date_complaint_recvd"))
Response.Write ("<br>")
Response.Write ("Site: " & rsCustomerComplaint("site"))
Response.Write ("<br>")
Response.Write ("Site Manager (At Time Of Incident): " & rsCustomerComplaint("site_manager"))
Response.Write ("<br>")
Response.Write ("Person Taking Complaint: " & rsCustomerComplaint("person_taking_complaint"))
Response.Write ("<br>")
Response.Write ("Email Address Of Person Submitting Form: " & rsCustomerComplaint("site_email"))
Response.Write ("<br>")
Response.Write ("Customer Name: " & rsCustomerComplaint("cust_name"))
Response.Write ("<br>")
Response.Write ("Customer Address: " & rsCustomerComplaint("cust_address"))
Response.Write ("<br>")
Response.Write ("Customer City: " & rsCustomerComplaint("cust_city"))
Response.Write ("<br>")
Response.Write ("Customer State: " & rsCustomerComplaint("cust_state"))
Response.Write ("<br>")
Response.Write ("Customer Zip Code: " & rsCustomerComplaint("cust_zip"))
Response.Write ("<br>")
Response.Write ("Customer Email Address: " & rsCustomerComplaint("cust_email"))
Response.Write ("<br>")
Response.Write ("Complaint Came To Us Via: " & rsCustomerComplaint("how_complaint_came"))
Response.Write ("<br>")
Response.Write ("What Happened: " & rsCustomerComplaint("site"))
Response.Write ("<br>")
Response.Write ("Site: " & rsCustomerComplaint("what_happened"))
Response.Write ("<br>")
Response.Write ("Staff Involved: " & rsCustomerComplaint("staff_involved"))
Response.Write ("<br>")
Response.Write ("Steps Taken To Remedy The Problem: " & rsCustomerComplaint("resolution_steps"))
Response.Write ("<br>")
Response.Write ("How Long Did It Take For Complaint To Be Resolved (If Resolved): " & rsCustomerComplaint("how_long_to_resolve"))
Response.Write ("<br>")
Response.Write ("Notes: " & rsCustomerComplaint("notes"))
Response.Write ("")
'Move to the next record in the recordset
rsCustomerComplaint.MoveNext
Loop
End If
'Reset server variables and close server objects and server side scripting
rsCustomerComplaint.Close
adoCon.close
Set rsCustomerComplaint = Nothing
Set adoCon = Nothing
%>
</BODY>
</HTML>
-------------------END CODE--------------------------------
Any help would be very appreciated. Thank you.
Mark Gordon
Displaying Records From SELECT dropdown
