Butler Bros
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)
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.SqlE xception: 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.SqlC onnection. OnError(Sq lException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3189408
System.Data.SqlClient.TdsP arser.Thro wException AndWarning (TdsParser StateObjec t stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +753
System.Data.SqlClient.TdsP arser.TryR un(RunBeha vior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +5042
System.Data.SqlClient.SqlC ommand.Fin ishExecute Reader(Sql DataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryp tion) +480
System.Data.SqlClient.SqlC ommand.Run ExecuteRea derTds(Com mandBehavi or cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptio nRequest) +3136
System.Data.SqlClient.SqlC ommand.Run ExecuteRea der(Comman dBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +691
System.Data.SqlClient.SqlC ommand.Int ernalExecu teNonQuery (TaskCompl etionSourc e`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +508
System.Data.SqlClient.SqlC ommand.Exe cuteNonQue ry() +391
System.Web.UI.WebControls. SqlDataSou rceView.Ex ecuteDbCom mand(DbCom mand command, DataSourceOperation operation) +590
System.Web.UI.DataSourceVi ew.Update( IDictionar y keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCal lback callback) +171
System.Web.UI.WebControls. GridView.H andleUpdat e(GridView Row row, Int32 rowIndex, Boolean causesValidation) +1498
System.Web.UI.WebControls. GridView.H andleEvent (EventArgs e, Boolean causesValidation, String validationGroup) +1008
System.Web.UI.Control.Rais eBubbleEve nt(Object source, EventArgs args) +50
System.Web.UI.WebControls. GridViewRo w.OnBubble Event(Obje ct source, EventArgs e) +138
System.Web.UI.Control.Rais eBubbleEve nt(Object source, EventArgs args) +50
System.Web.UI.Page.Process RequestMai n(Boolean includeStagesBeforeAsyncPo int, Boolean includeStagesAfterAsyncPoi nt) +5062
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.7.2110.0
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
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.SqlE
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.SqlC
System.Data.SqlClient.TdsP
System.Data.SqlClient.TdsP
System.Data.SqlClient.SqlC
System.Data.SqlClient.SqlC
System.Data.SqlClient.SqlC
System.Data.SqlClient.SqlC
System.Data.SqlClient.SqlC
System.Web.UI.WebControls.
System.Web.UI.DataSourceVi
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.Control.Rais
System.Web.UI.WebControls.
System.Web.UI.Control.Rais
System.Web.UI.Page.Process
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.7.2110.0
What data type is SupplierID column?
ASKER
SupplierID column: nvarchar(50)
it's the PK
Rich
it's the PK
Rich
I've created following table in my testing database:
CREATE TABLE dbo.supplier_pricing_backu p_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_backu p_test VALUES ('00005', null, 'col1', 'col2', 'col3')
Following statement showed the data in SQL Server Management Studio:
SELECT * FROM dbo.supplier_pricing_backu p_test
Following program which is based on your code works without problems (please update the Connection string to your database):
CREATE TABLE dbo.supplier_pricing_backu
and added one row:
INSERT INTO dbo.supplier_pricing_backu
Following statement showed the data in SQL Server Management Studio:
SELECT * FROM dbo.supplier_pricing_backu
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
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.
ASKER
huh..
I just discovered that it posts that error before even hitting any GridView1_RowUpdating code..
weird
I'll look closer..
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.UpdateComma nd = Query
Do you need it at all?
SqlDataSource1.UpdateComma
Do you need it at all?
ASKER
if not,..
"Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified. "
I'm probably not organizing the code the best way..
;/
"Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified. "
I'm probably not organizing the code the best way..
;/
ASKER
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>
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.
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.
ASKER
still wrestling with it..
ASKER
interesting:
your code runs, gives an 'ok' but doesn't update the table
I've got all kinds of rights on the database..
hmm
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I figured that:
was explicitly assigning a supplierID value..
maybe it's time to call it a day here...
;O
Dim MySupplierID As String = "00005"
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.
ASKER
still struggling.. but I'm trying things..
ASKER
thanks much for the help.
I'll post a new thread if I cannot make it work without the message..
I'll post a new thread if I cannot make it work without the message..