Question?

Calibus · August 5th, 2004, 09:20 AM

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>

bmains · August 5th, 2004, 09:39 AM

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

Calibus · August 5th, 2004, 10:00 AM

Thank you I will try That!

Calibus · August 5th, 2004, 01:47 PM

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> </p>
        <p align="center">

        Access Database
<p> </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> </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>   

    <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"> 

    <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>

bmains · August 5th, 2004, 02:45 PM

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

Calibus · August 5th, 2004, 02:58 PM

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>

<%

Calibus · August 5th, 2004, 03:51 PM

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> </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>   

    <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"> 

    <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>

rajanikrishna · August 5th, 2004, 08:08 PM

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"

bmains · August 6th, 2004, 08:25 AM

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