RobColborne
MIS
Hi
I am trying to create a very simple ASP page that takes a value from a form into a variable and then displays the results of an SQL query based on that variable. The result should just be a single record. The problem I am having is that the ASP page produces no results.
For testing purposes I outputted the contents of the variable and SQL query used to ensure these are correct, and they are.
If I changed the SQL query so that it does not use a Where clause, I would expect that the output would be all records in the database. Which is what I get, so this works.
So the variable is correct, the SQL query is correct. But the results are not being produced, I can't see what is wrong.
The database I am connecting to is SQL.
The code I am using is as follows:
Thanks
Rob
I am trying to create a very simple ASP page that takes a value from a form into a variable and then displays the results of an SQL query based on that variable. The result should just be a single record. The problem I am having is that the ASP page produces no results.
For testing purposes I outputted the contents of the variable and SQL query used to ensure these are correct, and they are.
If I changed the SQL query so that it does not use a Where clause, I would expect that the output would be all records in the database. Which is what I get, so this works.
So the variable is correct, the SQL query is correct. But the results are not being produced, I can't see what is wrong.
The database I am connecting to is SQL.
The code I am using is as follows:
Code:
<%@ Language=VBScript %>
<%
Option Explicit
%>
<%
'Declare variables
Dim rsGoldMine 'as adodb.recordset
Dim sGoldMineACCOUNTNO 'GoldMine AccountNo
Dim sGoldmineConnectionString 'as Connection string
Dim cnnGoldmine 'as connection
Dim sGoldmineDatabaseName 'as database string
Dim strSQL 'String to hold SQL query
'Populate Variables
sGoldmineConnectionString="Provider=MSDataShape;DSN=GoldMine;UID=sa;PWD="
sGoldmineDatabaseName="GMDemo"
sGoldMineACCOUNTNO=Request.Form("ACCOUNTNO")
'Connection to Database
Set cnnGoldmine = Server.CreateObject("ADODB.Connection")
cnnGoldmine.Open sGoldmineConnectionString
set rsGoldMine = Server.CreateObject("ADODB.Recordset")
rsGoldMine.ActiveConnection=cnnGoldmine
'[COLOR=red ]The query should pull in the variable to produce a restricted result, if query is change to simply Select * from contact1 it produces results[/color]
strSQL = "select COMPANY from Contact1 Where Accountno ='" & sGoldMineACCOUNTNO & "'"
rsGoldMine.Open strSQL, cnnGoldMine
'[COLOR=red ] The variables get outputted correctly [/color]
Response.Write (sGoldMineACCOUNTNO) & "<BR>" 'Output the variable populated from form
Response.Write (strSQL) 'Output SQL query string to check
%>
<html>
<head>
</head>
<%
WHILE not rsGoldMine.EOF
Response.Write ("<br>")
Response.Write (rsGoldMine("COMPANY"))
Response.Write ("<br>")
rsGoldMine.MoveNext
WEND
'Reset server variables
cnnGoldmine.Close
Set cnnGoldmine= Nothing
Set rsGoldMine = Nothing
%>
</body>
</html>Thanks
Rob
