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

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

August 5th, 2004, 09:39 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
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
|

August 5th, 2004, 10:00 AM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thank you I will try That!
|

August 5th, 2004, 01:47 PM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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>
|

August 5th, 2004, 02:45 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
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
|

August 5th, 2004, 02:58 PM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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>
<%
|

August 5th, 2004, 03:51 PM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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>
|

August 5th, 2004, 08:08 PM
|
Friend of Wrox
|
|
Join Date: Sep 2003
Posts: 363
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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"
|

August 6th, 2004, 08:25 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
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 |
|
 |