Cannot update SQL from ASP.NET GridView values

I'm trying to update a SQL database(2008R2), with values from my GridView

I am getting the following as soon as I click on the 'UPDATE' link on my GridView

Object reference not set to an instance of an object.
  Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:
Line 38:         cmd.Parameters.AddWithValue("@supplierID", GridView1.SelectedRow.Cells(0).Text)


 
Here's my form section of ASPX:
  <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="SupplierID" DataSourceID="SqlDataSource1" Height="494px" Width="479px" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" BackColor="#DBDBDB" OnRowUpdating="GridView1_RowUpdating">
                <Columns>
                    <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" ReadOnly="True" SortExpression="SupplierID" />
                    <asp:BoundField DataField="Supplier" HeaderText="Supplier" SortExpression="Supplier" ItemStyle-Wrap="False">
                        <ItemStyle Width="50px" />
                    </asp:BoundField>
                    <asp:BoundField DataField="Col1" HeaderText="Col1" SortExpression="Col1" >
                        <ItemStyle Width="50px" />
                    </asp:BoundField>
                   <asp:BoundField DataField="Col2" HeaderText="Col2" SortExpression="Col2" >
                                               <ItemStyle Width="50px" />
                   </asp:BoundField>
                    <asp:BoundField DataField="Col3" HeaderText="Col3" SortExpression="Col3" >
                                                <ItemStyle Width="50px" />
                    </asp:BoundField>
                </Columns>
                
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ReportServerConnectionString %>" SelectCommand="SELECT * FROM [supplier_pricing]"></asp:SqlDataSource>
            <asp:SqlDataSource ID="dsSuppliers" runat="server"></asp:SqlDataSource>
        </div>
    </form>

Open in new window



Thoughts?

Thanks,
Rich
Richard GouetteIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
can you post: GridView1_RowUpdating

from your code behind?
Richard GouetteIT ManagerAuthor Commented:
whoops, sorry I thought I had!

Dim sqlConn As SqlConnection = New SqlConnection("Data Source=SQL1;Initial Catalog=ReportServer;Integrated Security=True;")


        Dim Query As New String("UPDATE supplier_pricing SET SupplierID = @supplierID,Supplier=@supplierName,Col1=@Col1,Col2=@Col2,Col3=@Col3")
        Dim cmd As SqlCommand = New SqlCommand(Query, sqlConn)

        cmd.Parameters.AddWithValue("@supplierID", GridView1.SelectedRow.Cells(0).Text)
        cmd.Parameters.AddWithValue("@supplierName", GridView1.SelectedRow.Cells(1).Text)
        cmd.Parameters.AddWithValue("@Col1", GridView1.SelectedRow.Cells(2).Text)
        cmd.Parameters.AddWithValue("@Col2", GridView1.SelectedRow.Cells(3).Text)
        cmd.Parameters.AddWithValue("@Col3", GridView1.SelectedRow.Cells(4).Text)

        cmd.Connection.Open()

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw New Exception(("Error " + ex.Message))
        End Try

Open in new window

Kyle AbrahamsSenior .Net DeveloperCommented:
The row most likely isn't selected.

Try this instead:
Dim gvr  as GridViewRow = CType(GridView1.Rows(e.RowIndex), GridViewRow)
cmd.Parameters.AddWithValue("@supplierID", gvr.Cells(0).Text)
'apply changes to other params.

Open in new window

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Richard GouetteIT ManagerAuthor Commented:
When I step through the code, it's posting the result of Cells 0 as nothing
It posts the results of Cells(1) as  the SupplierID
I thought this was a 0-based configuration?

Here's what I'm running currently:

Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs)
        Dim row As GridViewRow = CType(GridView1.Rows(e.RowIndex), GridViewRow)

        Dim sqlConn As SqlConnection = New SqlConnection("Data Source=SQL1;Initial Catalog=ReportServer;Integrated Security=True;")
        Dim Query As New String("UPDATE supplier_pricing_backup SET Supplier=@supplierName,Col1=@Col1,Col2=@Col2,Col3=@Col3 WHERE SupplierID = @SupplierID")
        Dim cmd As SqlCommand = New SqlCommand(Query, sqlConn)
        Dim gvr As GridViewRow = CType(GridView1.Rows(e.RowIndex), GridViewRow)

        'testing
        MsgBox("Cells 0 : " + gvr.Cells(0).Text)
        MsgBox("Cells 1 : " + gvr.Cells(1).Text)
        MsgBox("Cells 2 : " + gvr.Cells(2).Text)
        MsgBox("Cells 3 : " + gvr.Cells(3).Text)
        MsgBox("Cells 4 : " + gvr.Cells(4).Text)

        cmd.Parameters.AddWithValue("@supplierID", gvr.Cells(0).Text)
        cmd.Parameters.AddWithValue("@supplierName", gvr.Cells(1).Text)
        cmd.Parameters.AddWithValue("@Col1", gvr.Cells(2).Text)
        cmd.Parameters.AddWithValue("@Col2", gvr.Cells(3).Text)
        cmd.Parameters.AddWithValue("@Col3", gvr.Cells(4).Text)

        cmd.Connection.Open()

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw New Exception(("Error " + ex.Message))
        End Try
    End Sub

Open in new window

Kyle AbrahamsSenior .Net DeveloperCommented:
It definitely is a 0 based configuration which is kind of a mystery.

Can you do a  gvr.Cells(0).GetType() or see what controls are in Cells(0)?  It's possible that it could be the edit button or something.  (EG:  When you look at the grid in HTML . . . what's in the first <td>) ?
Richard GouetteIT ManagerAuthor Commented:
Here's the result of:        
MsgBox("Cell TypeOf: " + gvr.Cells(0).GetType().ToString)

Open in new window


Capture.PNG

Source View yields:

                  
<td><a href="javascript:__doPostBack(&#39;GridView1&#39;,&#39;Edit$0&#39;)">Edit</a>&nbsp;<a href="javascript:__doPostBack(&#39;GridView1&#39;,&#39;Delete$0&#39;)">Delete</a></td><td>102070</td><td style="width:50px;white-space:nowrap;">ABBOTT WORKHOLDING PRODUCTS</td><td style="width:50px;">26</td><td style="width:50px;">23</td><td style="width:50px;">20</td>

Open in new window



What say you?
Rich
Kyle AbrahamsSenior .Net DeveloperCommented:
Yep, (0) is the edit link cell.  

Comes from the AutoGenerateEditButton=true on the gridview.
Richard GouetteIT ManagerAuthor Commented:
I notice I get nothing returned from :
        MsgBox("Cells 2 : " + gvr.Cells(2).Text)
        MsgBox("Cells 3 : " + gvr.Cells(3).Text)
        MsgBox("Cells 4 : " + gvr.Cells(4).Text)
        MsgBox("Cells 5 : " + gvr.Cells(5).Text)

Open in new window


within the :
    Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs)

Open in new window

event handler.

Am I using the correct handler event type?
Kyle AbrahamsSenior .Net DeveloperCommented:
Did you set the edit index?  

If  you did you may have to cast the controls as a textbox for columns that aren't read only.  (EG: Supplier ID works because it's read only and you can edit the text.  The rest probably wouldn't because they are replaced with textboxes when you click edit.)
 TryCast(gvr.Cells(2).Controls(0), TextBox).Text

Open in new window


Please see this link for a full example:
https://www.aspsnippets.com/Articles/Edit-Update-and-Delete-in-ASPNet-GridView-with-AutoGenerateColumns-True-using-C-and-VBNet.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard GouetteIT ManagerAuthor Commented:
Kyle, that's working great, thank you.
Do you know why I get: "Must declare the scalar variable "@supplierName". "
even though the database update is successful?
That column(Supplier) is a nvarchar(MAX) as opposed to the other 3, which are varchar(50), but that's not it, is it?



 Dim sqlConn As SqlConnection = New SqlConnection("Data Source=SQL1;Initial Catalog=ReportServer;Integrated Security=True;")
        Dim Query As New String("UPDATE supplier_pricing_backup SET Supplier=@supplierName,Col1=@Col1,Col2=@Col2,Col3=@Col3 WHERE SupplierID = @SupplierID")
        Dim cmd As SqlCommand = New SqlCommand(Query, sqlConn)
        Dim gvr As GridViewRow = CType(GridView1.Rows(e.RowIndex), GridViewRow)
        SqlDataSource1.UpdateCommand = Query

        Dim MySupplierID As String = gvr.Cells(1).Text
        Dim MySupplier As String = TryCast(gvr.Cells(2).Controls(0), TextBox).Text
        Dim MyCol1 As String = TryCast(gvr.Cells(3).Controls(0), TextBox).Text
        Dim MyCol2 As String = TryCast(gvr.Cells(4).Controls(0), TextBox).Text
        Dim MyCol3 As String = TryCast(gvr.Cells(5).Controls(0), TextBox).Text

        cmd.Parameters.AddWithValue("@supplierID", MySupplierID)
        cmd.Parameters.AddWithValue("@supplierName", MySupplier)
        cmd.Parameters.AddWithValue("@Col1", MyCol1)
        cmd.Parameters.AddWithValue("@Col2", MyCol2)
        cmd.Parameters.AddWithValue("@Col3", MyCol3)

        cmd.Connection.Open()

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw New Exception(("Error " + ex.Message))
        End Try

Open in new window

Richard GouetteIT ManagerAuthor Commented:
thanks VERY much.
I'll post the other issue in a  new thread.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.