Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 5th, 2004, 09:20 AM
Authorized User
 
Join Date: Jun 2004
Location: miami, fl, USA.
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>




Reply With Quote
  #2 (permalink)  
Old August 5th, 2004, 09:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
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
Reply With Quote
  #3 (permalink)  
Old August 5th, 2004, 10:00 AM
Authorized User
 
Join Date: Jun 2004
Location: miami, fl, USA.
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you I will try That!

Reply With Quote
  #4 (permalink)  
Old August 5th, 2004, 01:47 PM
Authorized User
 
Join Date: Jun 2004
Location: miami, fl, USA.
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>

Reply With Quote
  #5 (permalink)  
Old August 5th, 2004, 02:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
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
Reply With Quote
  #6 (permalink)  
Old August 5th, 2004, 02:58 PM
Authorized User
 
Join Date: Jun 2004
Location: miami, fl, USA.
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>

<%



Reply With Quote
  #7 (permalink)  
Old August 5th, 2004, 03:51 PM
Authorized User
 
Join Date: Jun 2004
Location: miami, fl, USA.
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>




Reply With Quote
  #8 (permalink)  
Old August 5th, 2004, 08:08 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Mentiri, Muara, Brunei.
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"



Reply With Quote
  #9 (permalink)  
Old August 6th, 2004, 08:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:55 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.