Link to home
Start Free TrialLog in
Avatar of Butler Bros
Butler BrosFlag for United States of America

asked on

"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
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

What data type is SupplierID column?
Avatar of Butler Bros

ASKER

SupplierID column:  nvarchar(50)
it's the PK

Rich
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.
The error showed in your case just points to some typo bug.
huh..
I just discovered that it posts that  error before even hitting any GridView1_RowUpdating  code..
weird

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

SqlDataSource1.UpdateCommand = Query

Do you need it at all?
if not,..

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

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

;/
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

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.
still wrestling with it..
interesting:
your code runs, gives an 'ok' but doesn't update the table
I've got all kinds of rights on the database..

hmm
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Yes, I've used the constant value for testing.  Your final code must use GridView values for all variables in the UPDATE command.
still struggling.. but I'm trying things..
thanks much for the help.
I'll post a new thread if I cannot make it work without the message..