Stored procedure for update returns dataset

2 posts, 0 answers
  1. Laura
    Laura avatar
    131 posts
    Member since:
    Aug 2008

    Posted 06 Oct 2008 Link to this post

    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

  2. Kiara
    Kiara avatar
    309 posts
    Member since:
    Jul 2006

    Posted 09 Oct 2008 Link to this post

    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
Back to Top