'Must declare the scalar variable....' can't see why I get this on updating..

I want to use a DatagridView to perform updates to a SQL 2008 table using the built-in links(EDIT/UPDATE)
I cannot see why I get:   Must declare the scalar variable "@supplierName".
I have tried to set a breakpoint right under the event handler, but the error posts before that..
Table configuration:
Capture.PNG

Here is ASPX and Code behind:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="default.aspx.vb" Inherits="supplier_pricing_web._default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <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"  EnableSortingAndPagingCallbacks = "true">
                

                <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 %>"
                UpdateCommand="UPDATE dbo.supplier_pricing_dev SET Supplier=@supplierName,Col1=@Col1,Col2=@Col2,Col3=@Col3 WHERE SupplierID = @SupplierID"
                SelectCommand="SELECT TOP 5 * FROM [supplier_pricing_dev]">
            </asp:SqlDataSource>
            <asp:SqlDataSource ID="dsSuppliers" runat="server"></asp:SqlDataSource>
        </div>
    </form>
</body>
</html>


Imports System.Data.SqlClient

Public Class _default
    Inherits System.Web.UI.Page

    Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs)

        'my orig code
        Dim sqlConn As SqlConnection = New SqlConnection("Data Source=SQL1;Initial Catalog=ReportServer;Integrated Security=True;")
        Dim Query As New String("UPDATE dbo.supplier_pricing_dev 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)

        cmd.Connection.Open()

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

        'testing
        cmd.CommandType = CommandType.Text
        cmd.Parameters.Add("@supplierID", SqlDbType.NVarChar).Value = MySupplierID
        cmd.Parameters.Add("@supplierName", SqlDbType.NVarChar).Value = MySupplier
        cmd.Parameters.Add("@Col1", SqlDbType.NVarChar).Value = MyCol1
        cmd.Parameters.Add("@Col2", SqlDbType.NVarChar).Value = MyCol2
        cmd.Parameters.Add("@Col3", SqlDbType.NVarChar).Value = MyCol3

        Dim result As Integer
        Try
            result = cmd.ExecuteNonQuery()
            Console.WriteLine(result)
        Catch ex As Exception
            Throw New Exception(("Error " + ex.Message))
        End Try


    End Sub







































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

    '    Dim Query As New String("UPDATE supplier_pricing SET Supplier=@supplierName,Col1=@Col1,Col2=@Col2,Col3=@Col3 WHERE SupplierID = @supplierID")
    '    'Dim cmd As SqlCommand = New SqlCommand("UPDATE supplier_pricing SET Supplier=@Supplier,
    '    '                                      Col1=@Col1,Col2=@Col2,Col3=@Col3 
    '    '                                     WHERE SupplierID = @SupplierID", sqlConn)
    '    Dim cmd As SqlCommand = New SqlCommand(Query, sqlConn)
    '    Dim gvr As GridViewRow = CType(GridView1.Rows(e.RowIndex), GridViewRow)
    '    GridView1.EditIndex = -1
    '    SqlDataSource1.UpdateCommand = Query

    '    Dim MySupplierID As String = TryCast(gvr.Cells(0).Controls(0), TextBox).Text
    '    Dim MySupplier As String = TryCast(gvr.Cells(1).Controls(0), TextBox).Text
    '    Dim MyCol1 As String = TryCast(gvr.Cells(2).Controls(0), TextBox).Text
    '    Dim MyCol2 As String = TryCast(gvr.Cells(3).Controls(0), TextBox).Text
    '    Dim MyCol3 As String = TryCast(gvr.Cells(4).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
    '    'GridView1.DataBind()
    'End Sub


    ' Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    'Dim row As GridViewRow = CType(GridView1.Rows(e.RowIndex), GridViewRow)
    '    Dim id As Integer = Int32.Parse(GridView1.DataKeys(e.RowIndex).Value.ToString())
    '    Dim tname As String = (CType(row.Cells(0).Controls(0), TextBox)).Text
    '    Dim tques As String = (CType(row.Cells(1).Controls(0), TextBox)).Text
    '    Dim cmd As MySqlCommand = New MySqlCommand("update exam set name1=@name,ques=@ques where id = @id", con)
    '    cmd.Parameters.Add("@id", MySqlDbType.Int16).Value = id
    '    cmd.Parameters.Add("@name", MySqlDbType.VarChar, 30).Value = tname.Text.Trim()
    '    cmd.Parameters.Add("@ques", MySqlDbType.VarChar, 40).Value = tques.Text.Trim()
    '    con.Open()
    '    cmd.ExecuteNonQuery()
    '    GridView1.EditIndex = -1
    '    bind()
    ' End Sub

End Class

Open in new window

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.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You use that variable in UPDATE, but there is no data binding in the <Columns> section.
1
Mark WillsTopic AdvisorCommented:
It looks like you need to change "suppliername" to "supplier"
0

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:

It looks like you need to change "suppliername" to "supplier"

that's my parameter... couldn't that be anything I want?
0
Richard GouetteIT ManagerAuthor Commented:
wow, you nailed it amigo.
Thanks!!

I thought I could name that to whatever I wanted...but I guess not
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
As I told: the list in <Columns> need to define the same variables as used in the UPDATE, you cannot name them different. So if you use @suppliername, you need to rename all occurances of Supplier to SupplierName in
<asp:BoundField DataField="Supplier" HeaderText="Supplier" SortExpression="Supplier" ItemStyle-Wrap="False">
                        <ItemStyle Width="50px" />
                    </asp:BoundField>

Open in new window

or vice versa.
0
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.