asked on

Using GridView with Stored Procedures

I have created a GridView using the wizard. I don't know how I can write the Stored Procedure to enable updating. A normal select statement won't work because it needs to update the data. All of the columns should be editable. Could you please let me know how I can write a Stored Procedure to do this?

I have included additional details below:

I have enabled the auto the "autoGenerateEditButton" so that the GridView can modify the data. When I debug the program and use the edit button, I receive an error saying that the query won't allow updating. I tried to write a query like this:
UPDATE Table
SET Name =
What would I use for the SET statement? I wouldn't know what the values and using parameters for each column won't be good. VS2005 should be able to modify the data automatically. I need to use stored procedures and not just a query (I can place the query in a stored procedure).

DarkoLord

Hi,

the SqlDataSource has an option to automatically generate the neccessary update statements. In the "Configure Select Statement" screen, click the Advanced button and check the "Generate INSERT, DELETE and UPDATE statements" checkbox.

Darko

jjrr007

ASKER

Thanks DarkoLord. My work requires that we use stored procedures. I think using this method, I won't be using a stored procedure. Is there a way to get the query that this method would use.

I tried a variety of things to get the query that would be used, but I keep coming up with security errors. I need to update all of the columns. Could you please let me know? Thanks again.

DarkoLord

Sure. If you check that checkbox I told you to, you can see the generated UPDATE query in the SqlDataSource's UpdateCommand property. You can then that query to your new stored procedure (do not forget the parameters). Then, just change the SqlDataSource's UpdateCommand property to stored procedure's name and change UpdateCommandType property from Text to StoredProcedure.

Hope this helps.
Darko

jjrr007

ASKER

Thanks for your time. I have tried that and received the error listed in the snippet. What I did was use the code from the advanced update tab to create a new stored proecedure. Then I used that query for the update stored procedure.

I have attached a copy of the of the Select and Update statement stored procedure in the attached file. Also, just to save time, I have attached a copy of the aspx code. There isn't any c# code since I just used the wizard. Also, all of the columns in the table are listed.

Source Error: 
 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
 
Stack Trace: 
 
 
[FormatException: Input string was not in a correct format.]
   System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2755599
   System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +112
   System.String.System.IConvertible.ToInt32(IFormatProvider provider) +43
   System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +293
   System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges) +264
   System.Web.UI.WebControls.SqlDataSourceView.AddParameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, String oldValuesParameterFormatString) +557
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +369
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78
   System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1215
   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +837
   System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +117
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

Open in new window

UpdateGridView.txt

jjrr007

ASKER

I have removed two columns that were causing errors from the Stored Procedures and the table. I don't have any errors when I update the values in debug mode now.

However, I don't see the new values that I set after they were updated in debug mode. I don't see the new values in the GridView (after the update) or in the source table. Is there a setting that I need to set? To save time, I have attached the ASP code in the snippet. What do you suggest?

Thanks again!

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        &nbsp;&nbsp;
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AutoGenerateDeleteButton="True"
            AutoGenerateEditButton="True" AutoGenerateSelectButton="True" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False"
                    ReadOnly="True" SortExpression="ProductID" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="ProductNumber" HeaderText="ProductNumber"
                    SortExpression="ProductNumber" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="Color" HeaderText="Color"
                    SortExpression="Color" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksDWConnectionString %>"
            SelectCommand="Rick" SelectCommandType="StoredProcedure" UpdateCommand="RickUpdate"
            UpdateCommandType="StoredProcedure">
            <UpdateParameters>
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="ProductNumber" Type="String" />
                <asp:Parameter Name="Color" Type="String" />
                <asp:Parameter Name="ProductID" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
    
    </div>
    </form>
</body>
</html>

Open in new window

ASKER CERTIFIED SOLUTION

DarkoLord

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jjrr007

ASKER

That worked! Thanks.

I removed the read-only property from the ProductID column. Please feel free to try another question I asked:

https://www.experts-exchange.com/questions/23540684/Simple-Windows-ID-Security-on-WebForm.html