Hi
The page that suppose to diplay the data isn't working? When I put

Code:
Set ObjRS = Server.CreateObject("ADODB.Recordset")


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


objRS.Open strsql, objConn, 0, 1


Response.Write(strSQL)
Response.end

It diplays the following! SELECT Top 7 * FROM Escalation_Forms WHERE Escalation_Type = 'Eta' AND Status like 'Pending Research'

Which can only mean it has captured the information from the submission form. Then why is it that nothing is displaying?
Can be wrong with my SQL statement?

Thank you
Rest of code! This is not the submission form.

<%
Dim objConn, objRS, ObjField, strSQL




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

Dim SqlStatus


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

SqlStatus = " AND Status like '" & request("Status") & "'"

Else

  SqlStatus = " "

End If



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


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


objRS.Open strsql, objConn, 0, 1


Response.Write(strSQL)
Response.end







'Show records

%>

<p>&nbsp;</p>
<p>&nbsp;</p>
<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

'Loop through the records here


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

%>

</table>



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

%>

Hey,

Did you try running it against your database as a query and finding out? That will tell you if the query is OK. If results are returning, then it is something with your page.

Brian

Hi brian
I did check it through the query analyzer and it does exist. Did I left something out? Now the funny thing is when this :
Set ObjRS = Server.CreateObject("ADODB.Recordset")


strSql = "SELECT * FROM Escalation_Forms WHERE Escalation_Type = '"& Iselected & "'" & sqlStatus

objRS.Open strsql, objConn, 0, 1

Response.Write(strSQL)

Response.end

I get the following on screen.
SELECT * FROM Escalation_Forms WHERE Escalation_Type = 'Eta' AND Status = 'Pending Research'
Here's my submission code.

<form name= form1 action ="Start2.asp" menthod="Post">

    <p align="center">

    <b>[u]Word order type</u></b>
</p>
    <p align="center">
<select name="Escalation_Type" size="1">
<option selected>Choose One</option>
<option value= "Eta">Eta</option>
<option value ="HDTV Call Back">HDTV Call Back</option>
<option Value ="Supervisor Call Back" > Supervisor Call Back</option>
</select></p>


    <p align="center">
[u]<b>Job Status</b></u></p>
    <p align="center">



    &nbsp;<input type="radio" name="Status" value="Open"<%=check1%>><b>Open</b>&nbsp;
<input type="radio" name="status" value="Pending Research"<%=check2%>><b>Pending Research </b>
<input type="radio" name="status" value="Closed"<%=check3%>><b>Close</b>

</p>


<p align="center">



<p align="center">




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

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

And the page that display the data.
Code:
<%
Dim objConn, objRS, ObjField, strSQL




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

Dim SqlStatus, Iselected


Iselected = Request("Escalation_Type")

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

SqlStatus = " AND Status = '" & request("Status") & "'"

Else

  SqlStatus = " "

End If




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


strSql = "SELECT * FROM Escalation_Forms WHERE Escalation_Type = '"& Iselected & "'" & sqlStatus

objRS.Open strsql, objConn, 0, 1

Response.Write(strSQL)

Response.end





'Show records

%>

<p>&nbsp;</p>
<p>&nbsp;</p>
<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>

<%
if objRS.eof then
response.write("No records match your request")
end if



Do Until ObjRS.EOF

'Loop through the records here


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

%>
</TR>
</table>



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

%>

Hey Brian;

I believe that there may be a problem with my where clause. When I put the following sql statement it gave me all the data in the database.

Example:
strSql = "SELECT * FROM Escalation_Forms"
it displays all data. So how can I fix re-arrange my where clause so it can pull my data?

Thank you

Hey,

You know that you have Response.End uncommented in your first post... that should end the processing of your page. Could that be your problem?

Your previous query that was printed to the screen; I don't see anything wrong with it... maybe you want to make sure there is a value in the IsSelected value, and if not, show an error...

Brian

Brain

I checked it and it didn't make any difference! Is there another way that I can write my SQL statement to retrieve the Escalation_Type and the Status?

Thank you

Hey,

Are both fields required to return results? If both fields are required, I wouldn't enter the Status field as a separate part of the SQL string (in the variable sqlStatus). I would just do Escalation_type = '" & IsSelected & "' and Status = '" & Status & "'".

In addition, if your query doesn't return results, it has to be another problem with the code....

Brian