cant get sqldatasource to return results from a stored procedure

I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.

This is the stored procedure:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgo-- =============================================-- Author:Lance Colton-- Create date: 7/31/06-- Description:Find the contest winner-- =============================================ALTER PROCEDURE [dbo].[AppcheckContest]-- Add the parameters for the stored procedure here@.BeginDatedatetime ='1/1/2006', @.EndDatedatetime ='12/31/2006', @.SectionIDint = 10,@.WinnerIDint = 0OUTPUTASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT top 1 @.WinnerID = P.UserIDFROM cs_Posts PWHERE P.PostDateBETWEEN @.BeginDateAND @.EndDateAND P.SectionID = @.SectionIDAND P.UserID <> 2100-- I don't want to win my own contest...AND SettingsID = 1000-- This number could be different if i had more than one CS installed?AND IsApproved = 1ORDER BYNEWID()-- yes this is slow, but it works...RETURN @.WinnerIDEND

It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.

Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.

Here's the code from the aspx codebehind file:

Partial Class Contest Inherits System.Web.UI.Page Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click Dim stuff As New System.Web.UI.DataSourceSelectArguments SqlDataSource1.Select(stuff) SqlDataSource1.DataBind() lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString End SubEnd Class

As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @.WinnerID parameter, but instead it sets it to "WinnerID".

Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit.

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server"> <asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" /> <asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar> <asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa%>" SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False"> <SelectParameters> <asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" /> <asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" /> <asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" /> <asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>   <asp:Label ID="lblWinnerID" runat="server" Text="?"></asp:Label></asp:Content>

okay well i gave up on using a sqldatasource since nobody replied.

here's my final working code using an sqlCommand object instead:

PartialClass ContestInherits System.Web.UI.PageProtected Sub btnSelectWinner_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnSelectWinner.ClickDim objCommandAs New SqlClient.SqlCommandDim objConnAs New SqlClient.SqlConnectionDim strQueryResultAs New Object objConn.ConnectionString = ConfigurationManager.ConnectionStrings("appcheck-csConnectionString-sa").ConnectionString objCommand.Connection = objConn objCommand.CommandType = CommandType.StoredProcedure objCommand.CommandText ="AppcheckContest"Dim intWinnerIDParamAs New SqlClient.SqlParameter("@.WinnerID", SqlDbType.Int)Dim DateFromParamAs New SqlClient.SqlParameter("@.BeginDate", SqlDbType.DateTime)Dim DateToParamAs New SqlClient.SqlParameter("@.EndDate", SqlDbType.DateTime) intWinnerIDParam.Direction = ParameterDirection.Output DateFromParam.Direction = ParameterDirection.Input DateToParam.Direction = ParameterDirection.Input objCommand.Parameters.Add(intWinnerIDParam) DateFromParam.Value = Calendar_From.SelectedDate DateToParam.Value = Calendar_To.SelectedDate objCommand.Parameters.Add(DateFromParam) objCommand.Parameters.Add(DateToParam) objConn.Open() objCommand.ExecuteScalar() objConn.Close()Try lblWinnerID.Text ="Winner ID: <a target='_blank' href='http://forum.appcheck.net/web/user/Profile.aspx?UserID=" & intWinnerIDParam.Value & "'>" & intWinnerIDParam.Value & "</a>" Catch ex As Exception lblWinnerID.Text = "error"End Try End SubEnd Class

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server"> Contest Start Date:<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar> Contest End Date:<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar> <asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" /> <asp:Label ID="lblWinnerID" runat="server" Text="?"></asp:Label></asp:Content>

set ANSI_NULLSONset QUOTED_IDENTIFIERONgo-- =============================================-- Author:Lance Colton-- Create date: 7/31/06-- Description:Find the contest winner-- =============================================ALTER PROCEDURE [dbo].[AppcheckContest] @.BeginDatedatetime, @.EndDatedatetime, @.SectionIDint = 10,@.WinnerIDint = 0OUTPUTASBEGINSET NOCOUNT ON;SELECT top 1 @.WinnerID = P.UserIDFROM cs_Posts PWHERE P.PostDateBETWEEN @.BeginDateAND @.EndDateAND P.SectionID = @.SectionIDAND P.UserID <> 2100-- I don't want to win my own contest...AND SettingsID = 1000-- This number could be different if i had more than one CS installed?AND IsApproved = 1ORDER BYNEWID()-- yes this is slow, but it works...END

|||Hi,

I've had a similar issue for the last few days.
I only found 1 page that helped me solve it:
http://dotnetjunkies.com/QuickStartv20/util/srcview.aspx?path=~/aspnet/samples/data/RetValAndOutputParamsInsert.src

I hope this helps you.
Cheers
Luke

|||

The above posters link is correct. The output/return value parameters are only available during the "ED" events (Inserted/Updated/Deleted). So if you want them, you have to capture them during that event.

And just a FYI - NewID isn't random, not even close. Google a better random method, which usually involves taking the last 32 bits of newid and feeding it into the rand function.