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 5th, 2004, 09:20 AM
Authorized User
 
Join Date: Jun 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default Question?

I am looking for an example on how I can achieve this task. I have a drop down menu that the user is able to choose different work orders and that is working fine. Now I am thinking of incorporating three features to that application. I have three check boxes 1)label"Open" 2)lable"Pending Research" 3)lable "Close". If the user chooses the "Pending research" check box. I would like it to Pull the work order types and all pending research.

Code:

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

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<div align="center">
  <center>
  <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#000000" width="985" height="50" id="table1" bgcolor="#000000">
    <tr>
      <td width="983" height="50" align="center">
    <img border="0" src="LogoMasthead.gif" align="left" width="150" height="45"><img border="0" src="header_level3.jpg" width="828" height="48"></td>
    </tr>
  </table>
  </center>
</div>

<p> </p>
        <p align="center">

        Access Database
<p> </p>
<p>

<%

Dim objConn, objRS, SQLstring

set objConn=Server.CreateObject("ADODB.CONNECTION")

Objconn.Open "Provider=sqloledb;Data Source=Flmirsql02;Initial Catalog=Source_Forms;User Id=Source_Forms_User;Password=password;"


' Create recordset and retrieve values using the open connection

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


SQLstring = "SELECT Top 7 * FROM Escalation_Forms WHERE Escalation_Type='"& Replace(Request.QueryString("Escalation_Type"), "'", "''") &"'"
objRS.Open SQLstring, objConn, 0, 1
    ' Show selected record




        %>
        <div align="center">
        <table border="2">
            <tr>
                <th bgcolor="#000000"><strong>Ticket Number</strong></th>
                <th bgcolor="#000000"><strong>Escalation Type</strong></th>
                <th bgcolor="#000000"><strong>First Name</strong></th>
                <th bgcolor="#000000"><strong>Last Name</strong></th>
                <th bgcolor="#000000"><strong>Account Number</strong></th>
                <th bgcolor="#000000"><strong>Phone Number</strong></th>
                <th bgcolor="#000000"><strong>CAE Name</strong></th>
                <th bgcolor="#000000"><strong>Customer Comment</strong></th>
                <th bgcolor="#000000"><strong>Status</strong></th>
                <th bgcolor="#000000"><strong>Feed Back</strong></th>

                </tr>


<%
            Do Until objRS.EOF

                Response.Write ("<tr>")

                   Response.Write("<td><a href='Results.asp?TicketNum=" & objRS("Ticket_Number") & "'>" & objRS("Ticket_Number")& "</a></td>")


                Response.Write "<td>" & objRS("Escalation_Type") &"</td>"
                Response.Write "<td>" & objRS("First_Name") &"</td>"
                Response.Write "<td>" & objRS("Last_Name") & "</td>"
                Response.Write "<td>" & objRS("Account_Number") & "</td>"
                Response.Write "<td>" & objRS("Phone_Number") & "</td>"
                Response.Write "<td>" & objRS("Cae_Name") & "</td>"
                Response.Write "<td>" & objRS("Cust_Com") & "</tr></td>"
                Response.Write "<td>" & objRS("Status") & "</td>"
                Response.Write "<td>" & objRS("Wip_Com") & "</td>"

objRS.MoveNext

    Loop
    objRS.Close

%>

    </table>
        </div>


<%
objRS.Open "Escalation_Forms", objConn, 0, 1


' Loop through recordset and display results




If Not objRS.EOF Then




objRS.MoveFirst
' the form below calls this file only this time with an id in the QueryString
%>


<option value="<%= objRS.Fields("Escalation_Type")%>"></option>

<p> </p>


    <form action="Eta_database3.asp" method="get" style="text-align: center">

    <select name="Escalation_Type" size="1">
        <option selected>Choose One</option>
        <option>Eta</option>
        <option>HDTV Call Back</option>
        <option> Supervisor Call Back</option>
    </select>


 <b> Open:</b><input type="checkbox" name="Open" value="Status">

    <b>Pending Research:</b><input type="checkbox" name="Pending Research" value="status">

    <b>Close:</b><input type="checkbox" name="Close" value="Status"><p>


    <input type="submit" value="Submit"/>

    <INPUT type="reset" value="Clear"></p>

    </form>
    <%
End If

' Close Data Access Objects and free DB variables
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

</body>
</htm>




 
Old August 5th, 2004, 09:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

When the page loads, why not write out the research in the beginning, and hide it (using a div tag or something like that, and set style="display:none"). Then, when they check that option, use JavaScript to show/hide that div tag.

Brian
 
Old August 5th, 2004, 10:00 AM
Authorized User
 
Join Date: Jun 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you I will try That!

 
Old August 5th, 2004, 01:47 PM
Authorized User
 
Join Date: Jun 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I had some problem getting it to work. I saw this example in one of the books I purchase. Can you tell me Why am I getting a blank screen? For some reason it's not loading.

Code:
<%@ Language=VBScript %>
<%Option Explicit %>

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<div align="center">
  <center>
  <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#000000" width="985" height="50" id="table1" bgcolor="#000000">
    <tr>
      <td width="983" height="50" align="center">
    <img border="0" src="LogoMasthead.gif" align="left" width="150" height="45"><img border="0" src="header_level3.jpg" width="828" height="48"></td>
    </tr>
  </table>
  </center>
</div>

<p>&nbsp;</p>
        <p align="center">

        Access Database
<p>&nbsp;</p>
<p>

<%
Dim objConn, objRS, ObjField, SQLstring

Dim intCount, vbQuote

vbQuote = chr (34)

if Request.Form("Field").Count > 0 Then

SQLstring = " "

For intCount = 1 to Request.Form("Field").Count

SQLstring = SQLstring & Request.Form("Field") (intCount) & ", "

Next

SQLstring = Left(SQLstring, Len(SQLstring) - 2)


set objConn=Server.CreateObject("ADODB.CONNECTION")

ObjConn.Open "Provider=sqloledb;Data Source=Flmirsql02;Initial Catalog=Source_Forms;User Id=Source_Forms_User;Password=password;"



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

SQLstring = "SELECT Top 7 * FROM Escalation_Forms WHERE Escalation_Type='"& Replace(Request.QueryString("Escalation_Type"), "'", "''") &"'"
'Response.Write SQLstring
'Response.End


objRS.Open SQLstring, ObjConn, 0, 1






'Show selected record

%>

        <table border="2">
            <tr>
                <th bgcolor="#000000"><strong>Ticket Number</strong></th>
                <th bgcolor="#000000"><strong>Escalation Type</strong></th>
                <th bgcolor="#000000"><strong>First Name</strong></th>
                <th bgcolor="#000000"><strong>Last Name</strong></th>
                <th bgcolor="#000000"><strong>Account Number</strong></th>
                <th bgcolor="#000000"><strong>Phone Number</strong></th>
                <th bgcolor="#000000"><strong>CAE Name</strong></th>
                <th bgcolor="#000000"><strong>Customer Comment</strong></th>
                <th bgcolor="#000000"><strong>Status</strong></th>
                <th bgcolor="#000000"><strong>Feed Back</strong></th>
                </tr>

<%

Do Until objRS.EOF


                Response.Write ("<tr>")

                   Response.Write("<td><a href='Results.asp?TicketNum=" & objRS("Ticket_Number") & "'>" & objRS("Ticket_Number")& "</a></td>")


                Response.Write "<td>" & objRS("Escalation_Type") &"</td>"
                Response.Write "<td>" & objRS("First_Name") &"</td>"
                Response.Write "<td>" & objRS("Last_Name") & "</td>"
                Response.Write "<td>" & objRS("Account_Number") & "</td>"
                Response.Write "<td>" & objRS("Phone_Number") & "</td>"
                Response.Write "<td>" & objRS("Cae_Name") & "</td>"
                Response.Write "<td>" & objRS("Cust_Com") & "</tr></td>"
                Response.Write "<td>" & objRS("Status") & "</td>"
                Response.Write "<td>" & objRS("Wip_Com") & "</td>"
objRS.MoveNext

    Loop
        objRS.Close

%>

    </table>

<%


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

objRS.Open "Escalation_Forms", SQLstring, objConn, 0, 1

'Loop through recordset and display results




If Not objRS.EOF Then




objRS.MoveFirst
' the form below calls this file only this time with an id in the QueryString
%>



<p>&nbsp;</p>



    <form action="Eta_database4.asp" method="Post" style="text-align: center">

    <option value="<%= objRS.Fields("Escalation_Type") %>"></option>

    <select name="Escalation_Type" size="1">
        <option selected>Choose One</option>
        <option>Eta</option>
        <option>HDTV Call Back</option>
        <option> Supervisor Call Back</option>
    </select>&nbsp;&nbsp;&nbsp;


    <b>Open:</b><input type="checkbox" name="Field" value="Open">
<%
    Set objRS = Server.CreateObject("ADODB.Recordset")
    ObjRS.open "Escalation_Type", SQLstring, objConn, 0, 1

 for each ObjField in ObjRS.Fields
 Response.Write "<input type = checkbox name =" & vbQuote & "Field" & vbQuote & _
 "value=" & vbQuote & ObjField.Name & vbQuote & ">" & ObjField.Name

 Next

 ObjRS.Close
 Set ObjRS = Nothing

%>


<b>Pending Research:</b><input type="checkbox" name="Field" value="Pending Research">&nbsp;


    <b>Close:</b><input type="checkbox" name="Field" value="Close">


<p>

    <input type="submit" value="Submit"/>

    <INPUT type="reset" value="Clear"></p>

    </form>
<%




' Close Data Access Objects and free DB variables

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing
End If
End if
%>

</body>
</htm>

 
Old August 5th, 2004, 02:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

Which area of the code aren't you getting to work? You have two different areas that are writing the responses to a Recordset, and I can't tell which one you may have problems with...

Brian
 
Old August 5th, 2004, 02:58 PM
Authorized User
 
Join Date: Jun 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This Section should display the ouput Right?

<%
Dim objConn, objRS, ObjField, strSQL

Dim intCount, vbQuote

vbQuote = chr (34)

if Request.Form("Field").Count > 0 Then

strSQL = " "

For intCount = 1 to Request.Form("Field").Count

strSQL = strSQL & Request.Form("Field") (intCount) & ", "

Next

strSQL = Left(strSQL, Len(strSQL) - 2)


set objConn=Server.CreateObject("ADODB.CONNECTION")

ObjConn.Open "Provider=sqloledb;Data Source=Flmirsql02;Initial Catalog=Source_Forms;User Id=Source_Forms_User;Password=password;"


strSQL = "SELECT " & strSQL & "FROM Escalation_Forms"

If Request.Form("Escalation_Type") <> " " Then


strSQL = strSQL & " WHERE Escalation_Type LIKE '%" & Request.Form("Escalation_Type") & "%'"

End If

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

Set objRS = objConn.Execute(strSQL)

'Response.Write strSQL
'Response.End






'Show records

%>
<table border="2">
            <tr>
                <th bgcolor="#000000"><strong>Ticket Number</strong></th>
                <th bgcolor="#000000"><strong>Escalation Type</strong></th>
                <th bgcolor="#000000"><strong>First Name</strong></th>
                <th bgcolor="#000000"><strong>Last Name</strong></th>
                <th bgcolor="#000000"><strong>Account Number</strong></th>
                <th bgcolor="#000000"><strong>Phone Number</strong></th>
                <th bgcolor="#000000"><strong>CAE Name</strong></th>
                <th bgcolor="#000000"><strong>Customer Comment</strong></th>
                <th bgcolor="#000000"><strong>Status</strong></th>
                <th bgcolor="#000000"><strong>Feed Back</strong></th>
                </tr>

<%

Do Until objRS.EOF


                Response.Write ("<tr>")

                   Response.Write("<td><a href='Results.asp?TicketNum=" & objRS("Ticket_Number") & "'>" & objRS("Ticket_Number")& "</a></td>")
                Response.Write "<td>" & objRS("Escalation_Type") &"</td>"
                Response.Write "<td>" & objRS("First_Name") &"</td>"
                Response.Write "<td>" & objRS("Last_Name") & "</td>"
                Response.Write "<td>" & objRS("Account_Number") & "</td>"
                Response.Write "<td>" & objRS("Phone_Number") & "</td>"
                Response.Write "<td>" & objRS("Cae_Name") & "</td>"
                Response.Write "<td>" & objRS("Cust_Com") & "</tr></td>"
                Response.Write "<td>" & objRS("Status") & "</td>"
                Response.Write "<td>" & objRS("Wip_Com") & "</td>"
objRS.MoveNext

    Loop
        objRS.Close
        set ObjRS = Nothing
%>

    </table>

<%



 
Old August 5th, 2004, 03:51 PM
Authorized User
 
Join Date: Jun 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am getting the following error! Can you help debug it!

Provider error '80020005'

Type mismatch.

/forms/Eta_database4.asp, line 145

line 145 is : Set objRS = Server.CreateObject("ADODB.Recordset")
    ObjRS.open "status", strSQL, objConn, 0, 1

By the i have made some changes!
The rest of my code:
<%

Dim objConn, objRS, ObjField, strSQL

Dim intCount, vbQuote

vbQuote = chr (34)

set objConn=Server.CreateObject("ADODB.CONNECTION")

ObjConn.Open "Provider=sqloledb;Data Source=Flmirsql02;Initial Catalog=Source_Forms;User Id=Source_Forms_User;Password=password;"


If Request.Form("Escalation_Type") <> " " Then

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

strSQL = "SELECT Top 7 * FROM Escalation_Forms WHERE Escalation_Type ='"& Replace(Request.QueryString("Escalation_Type"), "'", "''") &"'"

objRS.Open strSQL, objConn, 0, 1

'Response.Write strSQL
'Response.End




'Show records

%>
<table border="2">
            <tr>
                <th bgcolor="#000000"><strong>Ticket Number</strong></th>
                <th bgcolor="#000000"><strong>Escalation Type</strong></th>
                <th bgcolor="#000000"><strong>First Name</strong></th>
                <th bgcolor="#000000"><strong>Last Name</strong></th>
                <th bgcolor="#000000"><strong>Account Number</strong></th>
                <th bgcolor="#000000"><strong>Phone Number</strong></th>
                <th bgcolor="#000000"><strong>CAE Name</strong></th>
                <th bgcolor="#000000"><strong>Customer Comment</strong></th>
                <th bgcolor="#000000"><strong>Status</strong></th>
                <th bgcolor="#000000"><strong>Feed Back</strong></th>
                </tr>

<%

Do Until objRS.EOF


                Response.Write ("<tr>")

                   Response.Write("<td><a href='Results.asp?TicketNum=" & objRS("Ticket_Number") & "'>" & objRS("Ticket_Number")& "</a></td>")
                Response.Write "<td>" & objRS("Escalation_Type") &"</td>"
                Response.Write "<td>" & objRS("First_Name") &"</td>"
                Response.Write "<td>" & objRS("Last_Name") & "</td>"
                Response.Write "<td>" & objRS("Account_Number") & "</td>"
                Response.Write "<td>" & objRS("Phone_Number") & "</td>"
                Response.Write "<td>" & objRS("Cae_Name") & "</td>"
                Response.Write "<td>" & objRS("Cust_Com") & "</tr></td>"
                Response.Write "<td>" & objRS("Status") & "</td>"
                Response.Write "<td>" & objRS("Wip_Com") & "</td>"
objRS.MoveNext

    Loop
        objRS.Close

%>

    </table>

<%



objRS.Open "Escalation_Forms", objConn, 0, 1



'Loop through recordset and display results




If Not objRS.EOF Then




objRS.MoveFirst
End if
'the form below calls this file only this time with an id in the QueryString
%>



<p>&nbsp;</p>



    <form action="Eta_database4.asp" method="get" style="text-align: center">

    <option value="<%= objRS.Fields("Escalation_Type") %>"></option>

    <select name="Escalation_Type" size="1">
        <option selected>Choose One</option>
        <option>Eta</option>
        <option>HDTV Call Back</option>
        <option> Supervisor Call Back</option>
    </select>&nbsp;&nbsp;&nbsp;


    <b>Open:</b><input type="checkbox" name="Field" value="Open">
<%
    Set objRS = Server.CreateObject("ADODB.Recordset")
    ObjRS.open "status", strSQL, objConn, 0, 1

 for each ObjField in ObjRS.Fields
Response.Write "<input type = checkbox name =" & vbQuote & "Field" & vbQuote & _
"value=" & vbQuote & ObjField.Name & vbQuote & ">" & ObjField.Name

Next

ObjRS.Close
Set ObjRS = Nothing

%>


<b>Pending Research:</b><input type="checkbox" name="Field" value="Pending Research">&nbsp;


    <b>Close:</b><input type="checkbox" name="Field" value="Close">


<p>

    <input type="submit" value="Submit"/>

    <INPUT type="reset" value="Clear"></p>

    </form>
<%




' Close Data Access Objects and free DB variables

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing
end if
%>

</body>
</htm>




 
Old August 5th, 2004, 08:08 PM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 363
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi,

  I think u r using SQL Server 2000

conn.ConnectionString="Provider=SQLOLEDB.1;Passwor d=pwd;Persist Security Info=True;User ID=userid;Initial Catalog=source_forms;Data Source=flmirsql02"



 
Old August 6th, 2004, 08:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

You got too many parameters here:

ObjRS.open "status", strSQL, objConn, 0, 1

"status" I belive must go. Is this a table name? You can't specify a table name and a SQL query.

Brian





Similar Threads
Thread Thread Starter Forum Replies Last Post
question maheshraju ASP.NET 2.0 Basics 3 March 13th, 2008 08:54 AM
Question Ashwini Classic ASP Databases 3 January 10th, 2006 07:20 AM
Question Ashwini Classic ASP Databases 1 January 4th, 2006 06:55 AM
a question gorji C++ Programming 2 August 11th, 2003 07:41 AM





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