Stored procedure for update returns dataset

[Laura]

The forum community has been so helpful so far, and I hope you can help me with the following problem I am having...

I am not doing automatic updates or inserts. I have a stored procedure executing in the code-behind to perform my update and inserts. Besides doing the save for the update or insert, the stored procedure returns a dataset with the status of the save and an message to print after the save (successful or an error message).

I use stored procedure for my select command also and I retrieve the results of the stored procedure for my grid, but how can I retrieve the results of the stored procedure doing the insert and the update? I am attaching my code for the aspx defining the dataset, and the codebehind for the insert and update.

Codebehind for update and insert:

 protected int Update_User_Calling_StoredProc(string tableName,  string packageCD, int sbOrderDetailID, int nUserID,string sLoginName,
                string sPassword, string sEmail, string sActive)
    {

        // Create Instance of Connection and Command Object
        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ContentCentralConnectionString"].ConnectionString);

        SqlCommand myCommand = new SqlCommand("isp_sbUpdateNewsTradePkgsSave", myConnection);
        // Mark the Command as a SPROC
        myCommand.CommandType = CommandType.StoredProcedure;

        // Add Parameters to SPROC
        SqlParameter parameterTableName = new SqlParameter("@temptablename", SqlDbType.NVarChar, 200);
        parameterTableName.Value = tableName;
        myCommand.Parameters.Add(parameterTableName);

        SqlParameter parameterODID = new SqlParameter("@sbODID", SqlDbType.Int, 4);
        parameterODID.Value = sbOrderDetailID;
        myCommand.Parameters.Add(parameterODID);

        SqlParameter parameterUserID = new SqlParameter("@user_id", SqlDbType.Int, 4);
        parameterUserID.Value = nUserID;
        myCommand.Parameters.Add(parameterUserID);

        SqlParameter parameterPackageCD = new SqlParameter("@package_cd", SqlDbType.NVarChar, 20);
        parameterPackageCD.Value = packageCD;
        myCommand.Parameters.Add(parameterPackageCD);

        SqlParameter parameterLoginName = new SqlParameter("@login_name", SqlDbType.NVarChar, 20);
        parameterLoginName.Value = sLoginName;
        myCommand.Parameters.Add(parameterLoginName);

        SqlParameter parameterPassword = new SqlParameter("@password", SqlDbType.NVarChar, 20);
        parameterPassword.Value = sPassword;
        myCommand.Parameters.Add(parameterPassword);

        SqlParameter parameterEmail = new SqlParameter("@email", SqlDbType.NVarChar, 20);
        parameterEmail.Value = sEmail;
        myCommand.Parameters.Add(parameterEmail);

        SqlParameter parameterActive = new SqlParameter("@active", SqlDbType.NVarChar, 20);
        parameterActive.Value = Convert.ToInt32(sActive.ToString());
        myCommand.Parameters.Add(parameterActive);

        try
        {
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();
            return (0);
        }
        catch (Exception e)
        {
            int x = 1;
            return (1);
        }
    }
    
    protected int Insert_User_Calling_StoredProc(string tableName, string sSAPID, string spackageCD, string sLoginName,string sPassword,
                   string sEmail, int nActive, int nOrderID, int nProductID )
    {

        // Create Instance of Connection and Command Object
        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ContentCentralConnectionString"].ConnectionString);

        SqlCommand myCommand = new SqlCommand("isp_sbInsertNewsTradePkgs", myConnection);
        // Mark the Command as a SPROC
        myCommand.CommandType = CommandType.StoredProcedure;

        // Add Parameters to SPROC
        SqlParameter parameterTableName = new SqlParameter("@temptablename", SqlDbType.NVarChar, 200);
        parameterTableName.Value = tableName;
        myCommand.Parameters.Add(parameterTableName);

        SqlParameter parameterSAPID = new SqlParameter("@SAPID", SqlDbType.NVarChar, 20);
        parameterSAPID.Value = sSAPID;
        myCommand.Parameters.Add(parameterSAPID);

        SqlParameter parameterPackageCD = new SqlParameter("@package_cd", SqlDbType.NVarChar, 20);
        parameterPackageCD.Value = spackageCD;
        myCommand.Parameters.Add(parameterPackageCD);

        SqlParameter parameterLoginName = new SqlParameter("@login_name", SqlDbType.NVarChar, 20);
        parameterLoginName.Value = sLoginName;
        myCommand.Parameters.Add(parameterLoginName);

        SqlParameter parameterPassword = new SqlParameter("@password", SqlDbType.NVarChar, 20);
        parameterPassword.Value = sPassword;
        myCommand.Parameters.Add(parameterPassword);

        SqlParameter parameterEmail = new SqlParameter("@email", SqlDbType.NVarChar, 20);
        parameterEmail.Value = sEmail;
        myCommand.Parameters.Add(parameterEmail);

        SqlParameter parameterActive = new SqlParameter("@active", SqlDbType.Int, 4);
        parameterActive.Value = nActive;
        myCommand.Parameters.Add(parameterActive);

        SqlParameter parameterOrderID = new SqlParameter("@sbOrderID", SqlDbType.Int, 4);
        parameterOrderID.Value = nOrderID;
        myCommand.Parameters.Add(parameterOrderID);

        SqlParameter parameterProductID = new SqlParameter("@sbProductID", SqlDbType.Int, 4);
        parameterProductID.Value = nProductID;
        myCommand.Parameters.Add(parameterProductID);


        try
        {
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();
            return (0);
        }
        catch (Exception e)
        {
            int x = 1;
            return (1);
        }
    }



ASPX:


<asp:SqlDataSource ID="ContentCentralUsers" runat="server"
        ConnectionString="<%$ ConnectionStrings:ContentCentralConnectionString %>"
        
        SelectCommand="isp_sbLoadUserData"

        SelectCommandType="StoredProcedure" >
       
                
        <SelectParameters>
            <asp:QueryStringParameter Name="SAPID" QueryStringField="SAPID" Type="String" />
            <asp:QueryStringParameter Name="sbODID" QueryStringField="sbODID" Type="Int32" />
        </SelectParameters>
        

        
    </asp:SqlDataSource>


<telerik:RadGrid ID="usersGrid" runat="server"  
       DataSourceID="ContentCentralUsers" GridLines="None" Skin="Office2007"
            AutoGenerateColumns="False"
            ondatabound="usersGrid_DataBound" onitemdatabound="usersGrid_ItemDataBound"
                       onitemevent="usersGrid_ItemDataBound"
                oniteminserted="usersGrid_ItemInserted" onitemupdated="usersGrid_ItemUpdated"
                onload="usersGrid_Load" ondatabinding="usersGrid_DataBinding"  ShowStatusBar="True"
                 OnPreRender="usersGrid_PreRender"
                oninsertcommand="usersGrid_InsertCommand" onitemcreated="usersGrid_ItemCreated"
                onpageindexchanged="usersGrid_PageIndexChanged" OnItemCommand="usersGrid_ItemCommand"
                onupdatecommand="usersGrid_UpdateCommand"   commanditemdisplay="TopAndBottom"
                onselectedindexchanged="usersGrid_SelectedIndexChanged" >
       
     
       <MasterTableView  DataSourceID="ContentCentralUsers"
             AllowFilteringByColumn="True" AllowSorting="True"   
              InsertItemPageIndexAction="ShowItemOnFirstPage" CommandItemDisplay="Top"
             NoMasterRecordsText="No users to display.">
            <CommandItemSettings AddNewRecordText="Add new user"  />
            
            <RowIndicatorColumn>
            <HeaderStyle Width="20px"></HeaderStyle>
            </RowIndicatorColumn>
   
            <ExpandCollapseColumn>
            <HeaderStyle Width="20px"></HeaderStyle>
            </ExpandCollapseColumn>
            
   
     <Columns>
        <telerik:GridEditCommandColumn ButtonType="ImageButton"  UniqueName="EditCommandColumn1">
                    <HeaderStyle Width="20px" />
        </telerik:GridEditCommandColumn>
        <telerik:GridBoundColumn DataField="user_id" UniqueName="user_id" Visible="false" ReadOnly="true" ></telerik:GridBoundColumn>
        <telerik:GridTemplateColumn HeaderText="Seat(User)" SortExpression="login_name" UniqueName="login_name" >
          <ItemTemplate>
             <asp:Label ID="login_name"   runat="server"  Text='<%#Eval("login_name") %>'></asp:Label>
          </ItemTemplate>
          <EditItemTemplate>
             <asp:TextBox Text='<%#Eval("login_name") %>'  runat="server"  OnTextChanged="change_loginName"></asp:TextBox>
          </EditItemTemplate>
        </telerik:GridTemplateColumn>
        
        <telerik:GridTemplateColumn HeaderText="Password" SortExpression="password" UniqueName="password"  EditFormColumnIndex="1" AllowFiltering="false">
          <ItemTemplate>
             <asp:Label ID="password"   runat="server"  Text='<%#Eval("password") %>'></asp:Label>
          </ItemTemplate>
          <EditItemTemplate>
             <asp:TextBox ID="TextBox1" Text='<%#Eval("password") %>'  runat="server"  OnTextChanged="change_Password" ></asp:TextBox>
          </EditItemTemplate>
        </telerik:GridTemplateColumn>
        
        <telerik:GridTemplateColumn HeaderText="Email" SortExpression="email" UniqueName="email" >
          <ItemTemplate>
             <asp:Label ID="email"   runat="server"  Text='<%#Eval("email") %>'></asp:Label>
          </ItemTemplate>
          <EditItemTemplate>
             <asp:TextBox ID="TextBox2" Text='<%#Eval("email") %>'  runat="server"  OnTextChanged="change_email"></asp:TextBox>
          </EditItemTemplate>
        </telerik:GridTemplateColumn>
        

        <telerik:GridTemplateColumn HeaderText="Package"  UniqueName="RadComboPackage" EditFormColumnIndex="1" >
                       <HeaderStyle Width="100%" />
                        <ItemTemplate>
                            <asp:Label runat="server" ID="Label1" Text='<%#Eval("package_name") %>'></asp:Label>
                            <telerik:RadToolTip runat="server" ID="RadToolTi

[Kiara]

Laura, although I am not an Sql expert and rarely use stored procedures for CRUD operations,  I find the project from here quite descriptive and it has SP for update/insert. Take a look at it and do the same for your programmatic data source update and insert parameters.

Hope this pointer is helpful.

Kiara