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
  3. UI for ASP.NET Ajax is Ready for VS 2017
Back to Top