'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?
 
Mark WillsTopic AdvisorCommented:
It looks like you need to change "suppliername" to "supplier"
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You use that variable in UPDATE, but there is no data binding in the <Columns> section.
1
 
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
 
QlemoBatchelor, 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.