asp.net 4.5 c# can't get sql stored procedure to call databind not working without binding to control? no return value either

Question

I'm using asp.net 4.5 and c# for my codebehind

I have a SqlDataSource that is not bound to a control; the purpose is - based on an action button i'll call a stored procedure. (I have another sqldatasource that fills a table, based on clicking a button w/in a row of that gridview the SP is supposed to execute using parameters from the gridview)

I've messed around with the definition of the data source

My questions/plea for help are:

Is my SP being called at all with DataBind ? (and maybe my parameters are ill formed) - or does it need an object on the asp page to be tied to? Is there a better way to call the sp?
How do I get/see the ReturnValue?
How can I see the SQL output? (or maybe I should just make that an input/output parameter of the SP)

Advance thanks for any help.

((latest version of asp sql source - with exec and parms in the select))

    <asp:SqlDataSource ID="sqlAlterStatus" runat="server" ConnectionString="<%$ ConnectionStrings:BOS_data.Prod %>" 
         SelectCommand="EXEC @ReturnValue = Customer_Contact_output.sp_PCC_Channel_Strategy_ltrs_alter_action_campaign_wth @FILE_ID, @Campaign_Name, @UPSCheck, @ACTION, @ECODE, @FULLNAME" 
         SelectCommandType="StoredProcedure">
         <SelectParameters>
             <asp:Parameter Name="FILE_ID" Type="Int32"  />
             <asp:Parameter Name="Campaign_Name" Type="String" />
             <asp:Parameter Name="UPSCheck" Type="Boolean" />
             <asp:Parameter Name="ACTION" Type="Byte" />
             <asp:Parameter Name="ECODE" Type="String" />
             <asp:Parameter Name="FULLNAME" Type="String" />
             <asp:Parameter Name="ReturnValue" Type="Int16" Direction="ReturnValue" />
         </SelectParameters>
</asp:SqlDataSource>

((and my original - parms not in select))

         <asp:SqlDataSource ID="sqlAlterStatus" runat="server" ConnectionString="<%$ ConnectionStrings:BOS_data.Prod %>" 
         SelectCommand="Customer_Contact_output.sp_PCC_Channel_Strategy_ltrs_alter_action_campaign_wth" SelectCommandType="StoredProcedure">
         <SelectParameters>
             <asp:Parameter Name="FILE_ID" Type="Int32"  />
             <asp:Parameter Name="Campaign_Name" Type="String" />
             <asp:Parameter Name="UPSCheck" Type="Boolean" />
             <asp:Parameter Name="ACTION" Type="Byte" />
             <asp:Parameter Name="ECODE" Type="String" />
             <asp:Parameter Name="FULLNAME" Type="String" />
             <asp:Parameter Name="ReturnValue" Type="Int16" Direction="ReturnValue" />
         </SelectParameters>
</asp:SqlDataSource>

The SP is mostly simple - definition:

ALTER PROCEDURE [Customer_Contact_output].[sp_PCC_Channel_Strategy_ltrs_alter_action_campaign_wth]  
          @FILE_ID BIGINT
        , @Campaign_Name VARCHAR(50)
        , @UPSCheck BIT
        , @ACTION TINYINT
        , @ECODE VARCHAR(6)
        , @FULLNAME VARCHAR(255)

after validation/updates/inserts there is a simple select (for a message - which I can't figure how to capture either) and return value -- like this:

SELECT 'FAIL - INVALID PARAMETER (CAMPAIGN NAME)' AS RETURN_MESSAGE
RETURN 0 -- FAIL

I'm trying to call the SP from the RowCommand on the gridview ((I've also altered this a bunch of times ... mostly in setting the parms: I've used both .DefaultValue = something :: and .Equals(something)

                protected void grdPCCletters_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        // for the SP
        string strSuppress = "1";
        string strRelease = "8";
        // get all the variables for the stored procedure call

        string strCampaign = grdPCCletters.Rows[int.Parse(e.CommandArgument.ToString())].Cells[3].Text;
        //string strUPS = grdPCCletters.Rows[int.Parse(e.CommandArgument.ToString())].Cells[4].Text;
        bool boolUPS = Convert.ToBoolean(grdPCCletters.Rows[int.Parse(e.CommandArgument.ToString())].Cells[4].Text);
        int intCurrStatus = Convert.ToInt16(grdPCCletters.Rows[int.Parse(e.CommandArgument.ToString())].Cells[8].Text);
        Int64 intFileID = Convert.ToInt64( grdPCCletters.Rows[int.Parse(e.CommandArgument.ToString())].Cells[9].Text);
        string strFullName = grdPCCletters.Rows[int.Parse(e.CommandArgument.ToString())].Cells[10].Text;
        string strEcode = grdPCCletters.Rows[int.Parse(e.CommandArgument.ToString())].Cells[11].Text;

        //int intReturnValue;   --- GRRR, how to get the returnvalue from the sp??

        // sqlAlterStatus.SelectParameters.UpdateValues
        // these values apply regardless of the action
        sqlAlterStatus.SelectParameters["FILE_ID"].DefaultValue = intFileID.ToString();
        sqlAlterStatus.SelectParameters["Campaign_Name"].DefaultValue = strCampaign;
        sqlAlterStatus.SelectParameters["UPSCheck"].DefaultValue = Convert.ToInt16(boolUPS).ToString();
        sqlAlterStatus.SelectParameters["ECODE"].DefaultValue = strEcode;
        sqlAlterStatus.SelectParameters["FULLNAME"].DefaultValue = strFullName;

        {
            if (e.CommandName.ToString() == "Suppress")
            {
                //sqlAlterStatus - set parameters
                sqlAlterStatus.SelectParameters["ACTION"].DefaultValue = (Convert.ToInt16(strSuppress)).ToString();

                // execute SP
                sqlAlterStatus.DataBind();                    

                //intReturnValue = sqlAlterStatus.SelectParameters["ReturnValue"].ToString();
            }
            else if (e.CommandName.ToString() == "Release")
            {
                //sqlAlterStatus
                sqlAlterStatus.SelectParameters["ACTION"].Equals(Convert.ToInt16(strRelease));

                sqlAlterStatus.DataBind();
            }
        }  

    }

I usually go for .ExecuteNonQuery() method on the command object. http://stackoverflow.com/a/1260961/546375 — Alex M, Apr 17 '15 at 12:39
Hey Alex, that method doesn't show as being available - is that a pre 4.5 command? for whatever it's worth, I can (on another page) get an SP to execute - however it's bound to objects in a form..... — tracer, Apr 17 '15 at 12:49
does a breakpoint get hit if you set it on your sqlAlterStatus.DataBind(); ? — Alex M, Apr 17 '15 at 13:06
Yes, it hits it ... I just modified the SP to add some logging -- it never even makes it into the SP. — tracer, Apr 17 '15 at 13:13
OMG . what a ... the one param is a Boolean, and I thought I needed to convert it to a 1/0 to call the SP -- changing it to: sqlAlterStatus.SelectParameters["UPSCheck"].DefaultValue = boolUPS.ToString(); made it work -- good grief — tracer, Apr 17 '15 at 14:52

tracer · Answer 1 · 2015-04-17T17:34:04.670

OMG . what a ... the one parameter is a Boolean, and I tried to be smarter than visual studio ... I converted the t/f to a 1/0 to call the SP (cuz, well, that's how I'd call in SQL directly!)

changing it to:

sqlAlterStatus.SelectParameters["UPSCheck"].DefaultValue = boolUPS.ToString();

made it work -- good grief

Oh, and in my flipping back and forth, it also wouldn't work without something to be bound to .. like a form. :(

asp.net 4.5 c# can't get sql stored procedure to call databind not working without binding to control? no return value either

1 Answers1