ASP Page Not Recognising Dataset

[sjohnson1984]

Hello,

I have a stored procedure (SQL Server 2005) which is the following:

CREATE PROCEDURE [dbo].[ReturnCustDetailsFromTelNumber]
	@DatabaseName varchar(200),
	@Telephone varchar(20)
AS
	BEGIN
	DECLARE @UseStatement varchar(2000)
	SET @UseStatement = ('USE [' + @DatabaseName + ']')
	EXEC (@UseStatement + ' SELECT * from CustomerData where Telephone = ''' + @Telephone + '''')
	END

The aim of this is to be passed 2 parameters, database name (selecting by the user on-screen) and telephone; these parameters are used to pull the correct customers info from the dB.

Here is the presentation / data-access i.e. *.asp page:

<%
Err.clear
On Error Resume Next
SQLQuery = ""
Set db21 = Server.CreateObject( "ADODB.Connection" )
OdbcConStr = Session("DBConnection") & ""
db21.Open OdbcConStr
SQLQuery = "EXEC dbo.ReturnCustDetailsFromTelNumber" & " '" & Session("CustomerDatabase") & "', '" & Session("custTelephone") & "'" 
Session( "LastRunQuery" ) = SQLQuery

Set ReturnedCustomers = db21.Execute( SQLQuery )
%>

<%if Err.Number <> 0 then%>
<p>An Error Occured 1</p>
<%=Err.Description %>
    
<%else if ReturnedCustomers.BOF then %>
 <p>No customer found!</p>

<% else %>
<table>
<tr>
    <th>Forename</th>
    <th>Surname</th>
    <th>Address1</th>
    <th>Postcode</th>
</tr>
<%Do While Not ReturnedCustomers.EOF %>
    <tr>
        <td><%=ReturnedCustomers("P1Firstname") %></td>
        <td><%=ReturnedCustomers("P1Surname") %></td>
        <td><%=ReturnedCustomers("Address1") %></td>
        <td><%=ReturnedCustomers("Postcode") %></td>
    </tr>
<% ReturnedCustomers.MoveNext %>
<%Loop %>
<%End If %>
<%End If%>
<%ReturnedCustomers.close%>
<%db21.close%>

</table>

When printing the variable SQLQuery to screen then running through SQL Server Management Studio, there is data returned, however, here there is none. I am using the right database name, connection string etc i.e. I am getting no error, just a BOF, which I know is not the case for the data I am querying.

Is it apparent what the problem is in the above? I am pretty sure it's not the sproc as when run through SSMS it returns data.

Thanks in advance I hope!

J

[shweta123]

Hi,

You can try by not using Use Databsename command in your stored procedure.Inste ad of that you can create the stored procedure in the same datbase in which your tables are located.So that you do not have to use Use database command .

[sjohnson1984]

CREATE PROCEDURE [dbo].[ReturnCustDetailsFromTelNumber]
	-- Add the parameters for the stored procedure here
	@DatabaseName varchar(200),
	@Telephone varchar(20)
AS
	BEGIN

	SET NOCOUNT ON --!

	DECLARE @UseStatement varchar(2000)	
	
	SET @UseStatement = ('USE [' + @DatabaseName + ']')

	SET NOCOUNT OFF --!

	EXEC (@UseStatement + ' SELECT * from CustomerData where Telephone = ''' + @Telephone + '''')

	END

which is utilised by:

<!--'Pull the product info out into a dataset -->
<%
Err.clear
On Error Resume Next

'Create connection object
Set db1 = Server.CreateObject( "ADODB.Connection" )

'Pass connection string to session variable
OdbcConStr = Session("DBConnection") & ""

'open ADODB connection object
db1.Open OdbcConStr

'Pass telephone and dB to stored procedure
SQLQuery = "EXEC dbo.ReturnCustDetailsFromTelNumber" & " '" & Session("CustomerDatabase") & "', '" & Session("custTelephone") & "'" 

Session( "LastRunQuery" ) = SQLQuery

'create recordset object as the returned rowset from the executed sql
Set objRs = db1.Execute( SQLQuery )
%>

<!-- there is a general error-->
<%if Err.Number <> 0 then%>
    <center><h1>An Error Occured</h1></center>

<!--if no records are returned-->
<% else if objRs.BOF then %>
    <center><h1>An Error Occured 1</h1></center>
    
<%else %>

<!--Table headers-->
<table>
<tr>
    <th style="display: none;">ID</th>
    <th>Forename</th>
    <th>Surname</th>
    <th>Address1</th>
    <th>Postcode</th>
</tr>

<!--'else we have records, so spool through them creating a table row for each record-->
<% Do While Not objRs.EOF %>

    <tr>
        <td style="display: none;"><%=objRs("ID")%></td>
        <td width=15%><span><%=objRs("P1Firstname") %></span></td>
        <td width=30%><span><%=objRs("P1Surname") %></span></td>
        <td width=30%><span><%=objRs("Address1") %></span></td>
        <td width=15%><span><%=objRs("Postcode") %></span></td>
        <td witdth=10%><input type="radio" name="customerCheckbox" value="<%=objRs("ID") %>">Select</td>
    </tr>

<%
objRs.MoveNext
Loop
End If
End If
objRs.close
db1.close
%>

thanks...and to the previous post: the point of this sp is that it allows us to look up a customer in whatever dB is appropriate based on a selection by the user. this is ideal i.e. 1 sp against more than 1, surely 1 is better!

J

[DrBunchman]

Have you been able to resolve this problem yet?

[sjohnson1984]

...just cleaning up.

The SP is:

CREATE PROCEDURE [dbo].[ReturnCustDetailsFromTelNumber]
	@DatabaseName varchar(200),
	@Telephone varchar(20)
AS
	BEGIN

	DECLARE @UseStatement varchar(2000)	
	
	SET @UseStatement = ('USE [' + @DatabaseName + ']')

	SET NOCOUNT OFF

	EXEC (@UseStatement + ' SELECT * from CustomerData where Telephone = ''' + @Telephone + '''')

	END

and the script is:

<form name="customer" action="userlocate.asp" method="post">



<!--'Pull the product info out into a dataset -->
<%
Err.clear
On Error Resume Next

'Create connection object
Set db1 = Server.CreateObject( "ADODB.Connection" )

'Pass connection string to session variable
OdbcConStr = Session("DBConnection") & ""

'open ADODB connection object
db1.Open OdbcConStr

'Pass telephone and dB to stored procedure
SQLQuery = "EXEC dbo.ReturnCustDetailsFromTelNumber" & " '" & Session("CustomerDatabase") & "', '" & Session("custTelephone") & "'" 

Session( "LastRunQuery" ) = SQLQuery

'create recordset object as the returned rowset from the executed sql
Set objRs = db1.Execute( SQLQuery )
%>

<!-- there is a general error-->
<%if Err.Number <> 0 then%>
    <center><h1>An Error Occured</h1></center>
    <%=Err.Number & " " & Err.Description & " " & Session("LastRunQuery") %>

<!--if no records are returned-->
<% else if objRs.BOF then %>
    <div style="position: absolute; left: 35%; top: 40%; height: 100px; width: 413px; padding: 1em; border: solid 1px grey">
    <center>No records were returned for telephone <%=Session("custTelephone") %></center><br />
    <span>ERROR MESSAGE: </span><%=Session("LastRunQuery") %></span><br /><br />
    <center><a href="custtelinput.asp">Click here to search again</a></center><br />
    <center><a href="productlocate.asp">Click here to choose product again</a></center><br />
    </div>
    
<%else %>

<!--Table headers-->

<div style="position: absolute; left: 30%; top: 5%; height: 100px; width: 413px; padding: 1em; border: solid 1px grey; float:left;">
<span>Please select the customer...</span>


<table>
<tr>
    <th style="display: none;">ID</th>
    <th>Forename</th>
    <th>Surname</th>
    <th>Address1</th>
    <th>Postcode</th>
</tr>

<!--'else we have records, so spool through them creating a table row for each record-->
<% Do While Not objRs.EOF %>

    <tr>
        <td style="display: none;"><%=objRs("ID")%></td>
        <td width=15%><span><%=objRs("P1Firstname") %></span></td>
        <td width=30%><span><%=objRs("P1Surname") %></span></td>
        <td width=30%><span><%=objRs("Address1") %></span></td>
        <td width=15%><span><%=objRs("Postcode") %></span></td>
        <td width=10%><input type="radio" name="customerCheckbox" value="<%=objRs("ID") %>">Select</td>
    </tr>
<%Session("Records") = "yes" %>
<%
objRs.MoveNext
Loop
End If
End If
IF Session("Records") = "yes" THEN
Response.Write "</table></div><input type='submit' value='submit' style=' position: absolute; left: 72%; top: 5%; float:left;'>"
END IF
objRs.close
db1.close

%>

</table>

...I am unsure what didn't work about this, though the above now works if this helps anybody!

Thanks

J