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
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
1 Answer, 1 is accepted
0
Kiara
Top achievements
Rank 1
Rank 1
answered on 09 Oct 2008, 10:58 AM
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
Hope this pointer is helpful.
Kiara