Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. 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 Databases 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 August 2nd, 2005, 02:06 PM
Registered User
 
Join Date: Aug 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Displaying Records From SELECT dropdown

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
 
Old August 2nd, 2005, 02:30 PM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default

can you send a link so that I can get an idea of how you site is built?

I have a triple listbox combo with an iFrame. If you want I can post it, if that's what you need.

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm
 
Old August 2nd, 2005, 02:49 PM
Registered User
 
Join Date: Aug 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm sorry. It's on my Development server. I'm running XP and have IIS running on it so I try to do my development on it. I'd have to change alot of the connection to get it out onto the web.
:(

Mark Gordon
 
Old August 2nd, 2005, 02:54 PM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default

Ok give me a description of what you have. Whatever relates to the problem. I'm new myself as well. But whatever I have learned I'll let you know.

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm
 
Old August 2nd, 2005, 03:01 PM
Registered User
 
Join Date: Aug 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I appreciate that.

Essentially, my asp page "defaultselect.asp" should come up without any records displaying. It should populate the dropdown form, which it does.

If someone selects a site from the dropdown, the form performs a "GET". That should then change the SQL to include the site name. Once the code checks to see if Request.Querystring("site") is there, it should then print out all the records for that particular site.

It seems to hang on my SQL statement. The error I keep getting back, say, if I select "community pools" from the dropdown list is thus:

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'site=Community Pools'.

Line 37 is my strSQL which should be grabbing the site name from the dropdown form and performing that query.

Does this help?

Mark Gordon
 
Old August 2nd, 2005, 03:12 PM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default

Ok so the first page has a dropdown, that is dynamically linked to a table field which leads you to several URLs?

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm
 
Old August 2nd, 2005, 03:15 PM
Registered User
 
Join Date: Aug 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It is the only page. When you first visit it, it shows the select dropdown list. I can select one of the items, but it is supposed to refresh that page using a "GET" instead of a "POST". It should then write out the Drop Down Select list again, but underneath that, it would show all the records related to that selection made. On the same page.

Mark Gordon
 
Old August 2nd, 2005, 03:30 PM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default

Does your drpdown work first of all? If not, try this:
[code]
<%
            'This SQL statement creates a list of names from the fieldname
            SQL1 = "Select DISTINCT(fieldname) FROM tablename "
            rs.Open sql1,conn, 1
                %>
                <select name="fieldname" size="5" style="width:75;">
                <%
                Do until RS.eof
                    %>
                    <option <%if int(fieldname) = int(rs("fieldname")) then response.write "selected" end if%> value="<%=RS("fieldname")%>"><%=RS("fieldname")%></option>

                    <%
                    rs.movenext
                    if rs.eof then
                        exit do
                    end if
                Loop
                %>
                </select>
                <%
            rs.close
%>
[\code]

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm
 
Old August 2nd, 2005, 03:36 PM
Registered User
 
Join Date: Aug 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

My dropdown works. It is the only thing working. What doesn't work is when a selection has been made, then the error shows up.

Mark Gordon
 
Old August 2nd, 2005, 03:44 PM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default

Ok give me a sample of what you have in the dropdown. Between the <option></option>

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm





Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem in Displaying preselected data in dropdown deb_kareng ASP.NET 2.0 Professional 2 August 14th, 2007 05:00 AM
Displaying values in dropdown list from mysql db scoobie Beginning PHP 3 January 25th, 2005 12:09 PM
select value of a dropdown in a datagrid Sbartis ASP.NET 1.0 and 1.1 Professional 2 January 14th, 2005 05:19 PM
Problem displaying one of the records. mAdg3rr Classic ASP Databases 5 June 24th, 2003 08:56 PM





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