"Must declare the scalar variable ..." why do I get this?

Do you know why I get: "Must declare the scalar variable "@supplierName". "
even though the database update is successful?
The (Supplier) column is a nvarchar(MAX) as opposed to the other 3, which are varchar(50), but that's not it, is it?
(Full Error page below code)

 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





Server Error in '/' Application.

Must declare the scalar variable "@supplierName".
  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.Data.SqlClient.SqlException: Must declare the scalar variable "@supplierName".

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:



[SqlException (0x80131904): Must declare the scalar variable "@supplierName".]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3189408
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +753
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +5042
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) +480
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +3136
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +691
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +508
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +391
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +590
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +171
   System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1498
   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1008
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +50
   System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +138
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +50
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5062

 


Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.7.2110.0
Richard GouetteIT ManagerAsked:
Who is Participating?
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.

pcelbaCommented:
What data type is SupplierID column?
0
Richard GouetteIT ManagerAuthor Commented:
SupplierID column:  nvarchar(50)
it's the PK

Rich
0
pcelbaCommented:
I've created following table in my testing database:

CREATE TABLE dbo.supplier_pricing_backup_test (SupplierID nvarchar(50), Supplier nvarchar(max), Col1 varchar(50), Col2 varchar(50), Col3 varchar(50))

and added one row:

INSERT INTO dbo.supplier_pricing_backup_test VALUES ('00005', null, 'col1', 'col2', 'col3')

Following statement showed the data in SQL Server Management Studio:

SELECT * FROM dbo.supplier_pricing_backup_test

Following program which is based on your code works without problems (please update the Connection string to your database):
Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Dim sqlConn As SqlConnection = New SqlConnection("Data Source=.\SQL2008R2;Initial Catalog=TestDB;Integrated Security=True;")
        Dim Query As New String("UPDATE dbo.supplier_pricing_backup_test 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 = "00005"
        Dim MySupplier As String = "Some very long text for nvarcharmax column"
        Dim MyCol1 As String = "Col1 text"
        Dim MyCol2 As String = "Col2 text"
        Dim MyCol3 As String = "Col3 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()

        Dim result As Integer

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

End Module

Open in new window

You may use the above code to test the UPDATE command - just create Console VB.NET application and run it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pcelbaCommented:
The error showed in your case just points to some typo bug.
0
Richard GouetteIT ManagerAuthor Commented:
huh..
I just discovered that it posts that  error before even hitting any GridView1_RowUpdating  code..
weird

I'll look closer..
0
pcelbaCommented:
OK, the problem is in your command

SqlDataSource1.UpdateCommand = Query

Do you need it at all?
0
Richard GouetteIT ManagerAuthor Commented:
if not,..

"Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified. "

I'm probably not organizing the code the best way..

;/
0
Richard GouetteIT ManagerAuthor Commented:
in case it helps..

<%@ 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 = "false" 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>
</body>
</html>

Open in new window

0
pcelbaCommented:
An example of data updates from a GridView is e.g. here: https://www.codeproject.com/Questions/41497/VB-net-How-to-update-database-from-datagridview

It should be possible to use @parameters instead of the UPDATE/INSERT command building. (In Solution 2)

Your code does not update database from the GridView directly so you don't need UpdateCommand.
0
Richard GouetteIT ManagerAuthor Commented:
still wrestling with it..
0
Richard GouetteIT ManagerAuthor Commented:
interesting:
your code runs, gives an 'ok' but doesn't update the table
I've got all kinds of rights on the database..

hmm
0
pcelbaCommented:
The code updates just row(s) with given SupplierID.
The number of updated rows appears above the OK.

Exception is raised when you have no rights or the SQL command is invalid etc.

You may test the UPDATE command in SQL Server Management Studio.
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:
I figured  that:
Dim MySupplierID As String = "00005"

Open in new window


was explicitly assigning a supplierID value..

maybe it's time to call it a day here...

;O
0
pcelbaCommented:
Yes, I've used the constant value for testing.  Your final code must use GridView values for all variables in the UPDATE command.
0
Richard GouetteIT ManagerAuthor Commented:
still struggling.. but I'm trying things..
0
Richard GouetteIT ManagerAuthor Commented:
thanks much for the help.
I'll post a new thread if I cannot make it work without the message..
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.

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.