Hi everyone,
I have two ASP pages, one has a form that the user can enter data into. The second creates a connection to a SQL server database then creates a html table with the data and sends it back to the first page as a string. I'm using a Session variable to pass the SQL string from the first page to the second one. My problem is that I can't get anything to return back to the first page, even if I hard code the string value. Here is my code. I hope I explained this well enough.
First Page:
I have two ASP pages, one has a form that the user can enter data into. The second creates a connection to a SQL server database then creates a html table with the data and sends it back to the first page as a string. I'm using a Session variable to pass the SQL string from the first page to the second one. My problem is that I can't get anything to return back to the first page, even if I hard code the string value. Here is my code. I hope I explained this well enough.
First Page:
Code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/mgt/includes/createTable.asp"-->
<% on error resume next %>
<%
'Page Variables
Dim str_SQL 'SQL statement to execute '
Dim str_consID 'Consultant ID
Dim str_prodName 'Product Name
Dim sqlData
Dim numRecs
' Purpose: Establish recordset
' Parameters: None
' Returns: None
Sub createSQLString()
' Get the user inputs and build sql string
Call getInputs()
str_SQL = " SELECT order_summary.cons_id, order_summary.ordr_num, order_summary.ordr_inv_id, order_summary.orty_code," & _
" product.prod_num, product.prod_name_engl, order_item.oitm_qty_ordered, order_item.oitm_price_amt," & _
" order_summary.ordr_eff_date, order_summary.ordr_add_date" & _
" FROM order_summary" & _
" INNER JOIN order_item ON order_summary.ordr_num = order_item.ordr_num" & _
" INNER JOIN product ON order_item.prod_num = product.prod_num" & _
" WHERE (((order_summary.cons_id) Like " & "'" & "%" & str_consID & "%" & "'" & ")" & _
" AND ((product.prod_name_engl) Like " & "'" & "%" & str_ProdName & "%" & "'" & "))"
End Sub
' Purpose: Get the user inputed values
' Parameters: None
' Returns: None
Sub getInputs()
'get the values from the text boxes
str_consID = Trim(Request.Form("consID"))
str_prodName = Trim(Request.Form("prodName"))
End Sub
Sub createSessionVars()
SESSION("STRSQL") = str_SQL
Response.Write("Session: " & SESSION("STRSQL"))
End Sub
' Purpose: Call all other subs
Sub process()
Call createSQLString()
createSessionVars()
End Sub
%>
<% process() %>
<html>
<head>
<title>Listing by Consultant ID and Product Name</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form action="" method="post" enctype="application/x-[URL unfurl="true"]www-form-urlencoded">[/URL]
<table width="300" border="0">
<tr>
<td colspan="2"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Please enter the following:</font></td>
<td></td>
</tr>
<tr>
<td><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Consulant ID</font></td>
<td><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Product Name</font></td>
</tr>
<tr>
<td><input name="consID" type="text" size="25" maxlength="25"></td>
<td><input name="prodName" type="text" size="25" maxlength="25"></td>
</tr>
<tr align="center">
<td colspan="2">
<input name="submit" type="submit">
</td>
</tr>
</table>
</form>
<%
Response.Write(sql)
Response.Write("Consultant: " & str_consID & "<br>")
Response.Write("Product: " & str_prodName & "<br>")
Response.Write("Records Returned: " & numRecs & "<br>")
%>
<br>
<div align="left">
<%=createTable()%>
</div>
</body>
</html>
Second Page:
<%
Option Explicit
Function createTable
Dim sql
Dim sqlData
Dim numRecs
Dim i
Dim j
Dim numRows
Dim numCols
Dim fontsize
Dim border
Dim strTable
Dim xsize
sql = SESSION("STRSQL")
'create the ADO Connection and Recordset objects '
' make connection to the SQL Server
Set obj_conn = Server.CreateObject("ADODB.Connection")
obj_conn.Open "DSN=opstest;UID=opstest;PWD=opstest;DATABASE=opstest"
Set obj_recordset = Server.CreateObject("ADODB.Recordset")
obj_recordset.CursorLocation = 3 'adUseClient '
obj_recordset.Open sql, obj_conn, 3 'adOpenStatic '
' Check for any errors
If err.number <> 0 Then
TrapError Err.description
End If
fontsize = 1
border = 1
xsize = 300
numRows = UBound(sqlData, 2)
numCols = UBound(sqlData, 1)
sqlData = obj_recordset.GetRows()
numRecs = UBound(sqlData, 2)
''''''''''''''''''''''''''''''
' Column headings first row '
''''''''''''''''''''''''''''''
strTable = "<TABLE>"
for i = 0 to numRows
strTable = strTable & "<TR>"
for j = 0 to numCols
strTable = strTable & "<TD><FONT SIZE='" & fontsize & "'>" & sqlData(j,i) & "</FONT></TD>"
next
strTable = strTable & "</TR>"
next
strTable = strTable & "</TABLE>"
createTable = strTable
End Function
%>