SQL Server Stored Procedure & DataGrid

Hammy · November 14, 2004

This is driving me nuts. I have a simple table in a SQL Server 2000 db, with a simple Stored Procedure. I am trying to run the stored procedure via a web page, then display the results in a datagrid. I am getting no errors...also no data. The data I am entering for the first/last name is a field I KNOW should return a result....

My Stored Procedure....

CREATE PROCEDURE SelectIDFromName
@FirstName varChar,
@LastName varChar,
@ID int  OUTPUT
AS
SELECT  @ID =UserID
FROM tblUsers
WHERE FirstName = @FirstName
	AND LastName = @LastName
GO

My Pages code...

<%@ Page Language="VB" Debug="True" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<script runat="server">
dim Conn as new SQLConnection("Database=db; User ID=UID; Password=PWD")

Sub Submit(obj as Object, e as EventArgs)
	dim objCmd as SQLCommand = new SQLCommand("SelectIDFromName", Conn)
	dim objReader as SQLDataReader
	objCmd.CommandType = CommandType.StoredProcedure
	
	dim objParam as SQLParameter
	objParam = objCmd.Parameters.Add("@FirstName", SQLDBType.varChar)
	objParam.Direction = ParameterDirection.Input
	objParam.Value = tbFirst.Text
	
	objParam = objCmd.Parameters.Add("@LastName", SQLDBType.varChar)
	objParam.Direction = ParameterDirection.Input
	objParam.Value = tbLast.Text
	
	objParam = objCmd.Parameters.Add("@ID", SQLDBType.int)
	objParam.Direction = ParameterDirection.Output	
	dim strParam as String = objParam.Value
	
	try
		objCmd.Connection.Open
		objReader = objCmd.ExecuteReader
	catch ex as SQLException
		Response.Write(ex.Message)
	end try
	
	Response.Write("Value is " & strParam)
	
	DataGrid1.DataSource = objReader
	DataGrid1.DataBind()
	
	objCmd.Connection.Close()
	
End Sub

</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form runat="server">
	Enter a first name:
	<asp:TextBox runat="server" id="tbFirst" />
	Enter a Last Name: 
	<asp:TextBox runat="server" id="tbLast" />
	
	<asp:Button id="btSubmit" runat="server" Text="Lookup" OnClick="Submit" /><p>
	
	
	<asp:DataGrid ID="DataGrid1"
	 runat="server" 
	 BorderColor="#000000" 
	 GridLines="Vertical" 
	 CellPadding="4" 
	 CellSpacing="0" 
	 Width="100%" 
	 Font-Name="Arial" 
	 Font-Size="8pt" 
	 HeaderStyle-BackColor="#FFCCCC" 
	 ItemStyle-BackColor="#FFFFFF" 
	 AlternatingItemStyle-BackColor="#cccccc" 
	 AutoGenerateColumns="False">
		<Columns>
			<asp:BoundColumn DataField="ID" HeaderText="ID" />
		</Columns>
	</asp:DataGrid>
</form>
</body>
</html>

Can anyone please tell me why I am not getting a result to display either in the grid or the Response.Write?

TIA,

Hammy

Robby · November 14, 2004

Run this in SQL Server and see if the results are as expected... (Of course you may want to replace the vars with hard-coded values for the test run)

SELECT @ID =UserID

FROM tblUsers

WHERE FirstName = @FirstName

AND LastName = @LastName

Hammy · November 14, 2004

If I open the table in Query view, and use the following...

SELECT UserID

FROM tblUsers

WHERE (FirstName = 'Joe') AND (LastName = 'Blow')

It returns the expected UserID. I am not sure how to run with parameters from within SQL Server itself.

Hammy · November 14, 2004

OK, I am narrowing it down...I am bypassing the Stored Procedure and giving command text directly.

This code populates the ataGrid with te correct UserID...

<script runat="server">
dim Conn as new SQLConnection("Database=Banking; User ID=aspnet; Password=aspnet")

Sub Submit(obj as Object, e as EventArgs)
	dim objCmd as SQLCommand = new SQLCommand("SELECT UserID FROM tblUsers WHERE LastName='Blow' AND FirstName='Joe'", Conn)
	dim objReader as SQLDataReader
	
	dim objParam as SQLParameter
	objParam = objCmd.Parameters.Add("@FirstName", SQLDBType.varChar)
	objParam.Direction = ParameterDirection.Input
	objParam.Value = tbFirst.Text
	
	objParam = objCmd.Parameters.Add("@LastName", SQLDBType.varChar)
	objParam.Direction = ParameterDirection.Input
	objParam.Value = tbLast.Text
	
	objParam = objCmd.Parameters.Add("@ID", SQLDBType.int)
	objParam.Direction = ParameterDirection.Output	
	dim strParam as String = objParam.Value
	
	try
		objCmd.Connection.Open
		objReader = objCmd.ExecuteReader
	catch ex as SQLException
		Response.Write(ex.Message)
	end try
	
	Response.Write("Value is " & strParam)
	
	DataGrid1.DataSource = objReader
	DataGrid1.DataBind()
	
	objCmd.Connection.Close()
	
End Sub

</script>

This Code also works properly, now using the input parameters....

<script runat="server">
dim Conn as new SQLConnection("Database=Banking; User ID=aspnet; Password=aspnet")

Sub Submit(obj as Object, e as EventArgs)
	dim objCmd as SQLCommand = new SQLCommand("SELECT UserID FROM tblUsers WHERE LastName=@LastName AND FirstName=@FirstName", Conn)
	dim objReader as SQLDataReader
	
	dim objParam as SQLParameter
	objParam = objCmd.Parameters.Add("@FirstName", SQLDBType.varChar)
	objParam.Direction = ParameterDirection.Input
	objParam.Value = tbFirst.Text
	
	objParam = objCmd.Parameters.Add("@LastName", SQLDBType.varChar)
	objParam.Direction = ParameterDirection.Input
	objParam.Value = tbLast.Text
	
	objParam = objCmd.Parameters.Add("@ID", SQLDBType.int)
	objParam.Direction = ParameterDirection.Output	
	dim strParam as String = objParam.Value
	
	try
		objCmd.Connection.Open
		objReader = objCmd.ExecuteReader
	catch ex as SQLException
		Response.Write(ex.Message)
	end try
	
	Response.Write("Value is " & strParam)
	
	DataGrid1.DataSource = objReader
	DataGrid1.DataBind()
	
	objCmd.Connection.Close()
	
End Sub

</script>

However, if I try to go to output parameter as well, if stops displaying a result...

<script runat="server">
dim Conn as new SQLConnection("Database=Banking; User ID=aspnet; Password=aspnet")

Sub Submit(obj as Object, e as EventArgs)
	dim objCmd as SQLCommand = new SQLCommand("SELECT @ID=UserID FROM tblUsers WHERE LastName=@LastName AND FirstName=@FirstName", Conn)
	dim objReader as SQLDataReader
	
	dim objParam as SQLParameter
	objParam = objCmd.Parameters.Add("@FirstName", SQLDBType.varChar)
	objParam.Direction = ParameterDirection.Input
	objParam.Value = tbFirst.Text
	
	objParam = objCmd.Parameters.Add("@LastName", SQLDBType.varChar)
	objParam.Direction = ParameterDirection.Input
	objParam.Value = tbLast.Text
	
	objParam = objCmd.Parameters.Add("@ID", SQLDBType.int)
	objParam.Direction = ParameterDirection.Output	
	dim strParam as String = objParam.Value
	
	try
		objCmd.Connection.Open
		objReader = objCmd.ExecuteReader
	catch ex as SQLException
		Response.Write(ex.Message)
	end try
	
	Response.Write("Value is " & strParam)
	
	DataGrid1.DataSource = objReader
	DataGrid1.DataBind()
	
	objCmd.Connection.Close()
	
End Sub

</script>

So, though I still have no idea why, it must have something to do with the output parameter.

Hammy · November 15, 2004

OK, I solved it. Output parameters are not returned until AFTER the Reader is closed. So I moved my Response.Write(strParam) statement after the close calls and it worked. Now, I still don't know how you would bind these results to a datagrid (though I suppose there is no reason to) as the connection has to be open to bind, and closed to get the parameters.

Now, I guess it's just a question I am wondering how you would do it if you wanted to...if anyone has any ideas.

Sign In

SQL Server Stored Procedure & DataGrid

Recommended Posts

Hammy

Robby

Hammy

Hammy

Hammy

Join the conversation

Browse

Activity