none
SqlException (0x80131904): Incorrect syntax near 'nvarchar'. Must declare the scalar variable "@SoyDataID". when Updating Data from Gridview RRS feed

  • Question

  • Hi:

    I am using Visual Web Developer 2010 to develope a database and query and a page to modify the data.  I have seen similar problem in the forum, try the suggested solutions, but none is work.  I think the problem is that in the gridview.  When I modifiy a cell and click "UPDATE" this is the error I got

    Incorrect syntax near 'nvarchar'.
    Must declare the scalar variable "@SoyDataID".

    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: Incorrect syntax near 'nvarchar'.
    Must declare the scalar variable "@SoyDataID".

    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:  (I CUT THE COMPLETE STACK TO SAVE SPACE)


    This is the source code that I copied from the source Window:

     

     

    <asp:SqlDataSource ID="SqdsSoyData" runat="server"

     

     

    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

     

     

    DeleteCommand="DELETE FROM [SoydataExported$] WHERE [SoyDataID] = @SoyDataID"

     

     

    InsertCommand="INSERT INTO [SoydataExported$] ([Company], [Variety], [SB Type], [Maturity], [Yield], [Oil], [Protein], [ValuePerAcre], [PLOT], [year], [DataSourceLocation]) VALUES (@Company, @Variety, @SB_Type, @Maturity, @Yield, @Oil, @Protein, @ValuePerAcre, @PLOT, @year, @DataSourceLocation)"

     

     

    SelectCommand="SELECT SoyDataID+1 AS Nothing, SoyDataID, Company, Variety, [SB Type], Maturity, Yield, Oil, Protein, ValuePerAcre, PLOT, year, DataSourceLocation FROM SoydataExported$ WHERE SoyDataID = @SoyDataID"

     

     

     

    UpdateCommand="UPDATE [SoydataExported$] SET [Company] = @Company, [Variety] = @Variety, [SB Type] = @SB_Type, [Maturity] = @Maturity, [Yield] = @Yield, [Oil] = @Oil, [Protein] = @Protein, [ValuePerAcre] = @ValuePerAcre, [PLOT] = @PLOT, [year] = @year, [DataSourceLocation] = @DataSourceLocation WHERE SoyDataID = @SoyDataID">

    NOTE:  I DELETED INSERT AND DELETE PARAMETERS FOR SPACE

     

     

    updated pARAMETERS FOLLOW

    I have tried many things to no success.  Any hint/help please.

    Tuesday, February 8, 2011 7:05 PM

Answers

  • Hi Daniel,

    Welcome to comeback.

    1 、I have no experience about this, but you can check your sql version with the query "select @@version"

           You could have this problem at SQL forum.

    2、I'm not familier with the SSMS,I use the Sql Server Profiler as a monitor tool. It seems with some

         syntax error on the update query.

          Here is a sample I hope it would be help for you, and I sugges you with this answer at the

          asp.net fourm.

          http://www.codeproject.com/KB/aspnet/InsertSqlDataSource.aspx

         Have a good day.


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 11, 2011 8:32 AM
    Moderator

All replies

  • To follow up this is the Update parameters and gridview from the source Window

    =======================================================================

    <UpdateParameters>

     

     

    <asp:Parameter Name="Company" Type="String" />

     

     

    <asp:Parameter Name="Variety" Type="String" />

     

     

    <asp:Parameter Name="SB_Type" Type="String" />

     

     

    <asp:Parameter Name="Maturity" Type="Int32" />

     

     

    <asp:Parameter Name="Yield" Type="Double" />

     

     

    <asp:Parameter Name="Oil" Type="Double" />

     

     

    <asp:Parameter Name="Protein" Type="Double" />

     

     

    <asp:Parameter Name="ValuePerAcre" Type="Double" />

     

     

    <asp:Parameter Name="PLOT" Type="String" />

     

     

    <asp:Parameter Name="year" Type="Int32" />

     

     

    <asp:Parameter Name="DataSourceLocation" Type="String" />

     

     

    <asp:ControlParameter ControlID="GridView1" Name="SoyDataID"

     

     

    PropertyName="SelectedValue" Type="Int32" />

     

     

    </UpdateParameters>

    Thanks

    Tuesday, February 8, 2011 7:14 PM
  • Again for the gridview source code:

     

     

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

     

     

    DataKeyNames="SoyDataID" DataSourceID="SqdsSoyData">

     

     

    <Columns>

     

     

    <asp:CommandField ShowEditButton="True" />

     

     

    <asp:BoundField DataField="SoyDataID" HeaderText="SoyDataID"

     

     

    InsertVisible="False" ReadOnly="True" SortExpression="SoyDataID" />

     

     

    <asp:BoundField DataField="Company" HeaderText="Company"

     

     

    SortExpression="Company" />

     

     

    <asp:BoundField DataField="Variety" HeaderText="Variety"

     

     

    SortExpression="Variety" />

     

     

    <asp:BoundField DataField="SB Type" HeaderText="SB Type"

     

     

    SortExpression="SB Type" />

     

     

    <asp:BoundField DataField="Maturity" HeaderText="Maturity"

     

     

    SortExpression="Maturity" />

     

     

    <asp:BoundField DataField="Yield" HeaderText="Yield" SortExpression="Yield" />

     

     

    <asp:BoundField DataField="Oil" HeaderText="Oil" SortExpression="Oil" />

     

     

    <asp:BoundField DataField="Protein" HeaderText="Protein"

     

     

    SortExpression="Protein" />

     

     

    <asp:BoundField DataField="ValuePerAcre" HeaderText="ValuePerAcre"

     

     

    SortExpression="ValuePerAcre" />

     

     

    <asp:BoundField DataField="PLOT" HeaderText="PLOT" SortExpression="PLOT" />

     

     

    <asp:BoundField DataField="year" HeaderText="year" SortExpression="year" />

     

     

    <asp:BoundField DataField="DataSourceLocation" HeaderText="DataSourceLocation"

     

     

    SortExpression="DataSourceLocation" />

     

     

    </Columns>

     

     

    </asp:GridView>

    Please Note that Clicking the "DELETE" on the gridview is working just fine.  Therefore the @SOYDATAID sent by the gridview was just fine. I do not know why it is not for the "UPDATE" button.

     

    Thanks

     

     

     

     

     

     

     

     

     

    Tuesday, February 8, 2011 7:15 PM
  • Hi Daniel,

    Welcome to ado.net managed provider forum.

    Base your description, It seems have not the connection or command issues with the code.

    I would like you to use the Sql Server Profiler to monitor the database command track, I want to

    know whether the application was send the query to the databse and what was it(May be you could copy it and run at the database explore).

    If  there are any issues please let me know.

    Have a good day.


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 10, 2011 9:28 AM
    Moderator
  • David:

    Do you mean this trace stack?

    [SqlException (0x80131904): Incorrect syntax near 'nvarchar'.
    Must declare the scalar variable "@SoyDataID".]
      System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802
      System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584
      System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
      System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
      System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +215
      System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
      System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
      System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178
      System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
      System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +394
      System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +697
      System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +95
      System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1226
      System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +716
      System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
      System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
      System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +121
      System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
      System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +125
      System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +169
      System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +9
      System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
      System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176
      System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
    
    Why clicking "DELETE" on the gridview are working well while "Editing/UPDATING is not?" 
    While in both case they use the @SoyDataID from the gridview as the value of the parameter SoyDataID? 
    Thanks


    Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

    Thursday, February 10, 2011 8:41 PM
  • Hi Danial,

    Thank you for the reply, The exception is seems throwed out by the sqlcommand so could you get the commandtext.

    and I hope you to test the command to active the update by manual.

    Are there any issues please let me know and Get  the command traces with sql server profiler(Please show me :) ).

    http://databases.about.com/od/sqlserver/ht/trace.htm

    I hope these would be help you.

    Have a good day.

     


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 11, 2011 1:12 AM
    Moderator
  • David:

    Following your guide I have 2 problems:

    Problem #1.

    I am not sure why my Sql Server Management Studio (SSMS) does not have "SQL Server Preofiler" under the Tool Menu.  The way I can get into the profiler is by right clicking the server connection and choosing the "Activity Monitor", and click "Process".  Under the database dropdown list, then I can see (Unfortunately) only the system database , not the the databases that I am working with.  Perhaps because the data base that I am working with is only "attached" to the server, not residing in the server.  The database (MDF) file that I am using is located in the visual studio directory for the website that I am constructing. So..I essentially can not use the Profiler to trace process.  Any idea how can I get the profiler under the Tool Menu so I can follow the step as described in the link you pointed to me?

    Problem #2

    Using my database (the one attached to the server), I can generate simple "update Query" without using the parameters.  So Manually it seems the Update is working.  I tried this Query by providing a value = 2 for the parameter SoyDataID. and it changes the maturity from value 3 to 2.

    SELECT

     

    SoydataExported$.*

    FROM

     

    SoydataExported$

     

    UPDATE [SoydataExported$] SET Maturity = 2 WHERE SoyDataID = 2

     

     

    SELECT SoydataExported$.*

    FROM

     

    SoydataExported$

    To test manually the Update command and the Update parameters, I need to translate the html code to the sql.  Can you help/guide (perhaps to a link) where I can learn create sql for parameterized Update query?  How can you create a sql in SSMS for a grid view to fire up an event that cause the Update command to change data in a database?

    Thanks 

    Friday, February 11, 2011 5:16 AM
  • I thing the reply above is not appropriate in this forum. What is up?

    Thanks

    Friday, February 11, 2011 5:20 AM
  • Hi Daniel,

    Welcome to comeback.

    1 、I have no experience about this, but you can check your sql version with the query "select @@version"

           You could have this problem at SQL forum.

    2、I'm not familier with the SSMS,I use the Sql Server Profiler as a monitor tool. It seems with some

         syntax error on the update query.

          Here is a sample I hope it would be help for you, and I sugges you with this answer at the

          asp.net fourm.

          http://www.codeproject.com/KB/aspnet/InsertSqlDataSource.aspx

         Have a good day.


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 11, 2011 8:32 AM
    Moderator
  • David:

    Thanks for the link.  I have been thinking of the same line as suggested in the link that is generate a code in the source code  portion of the web page to fire an event after changing the value of the gridview cell. I will try that and see how it goes.

    Again, thanks for the link.

    • Proposed as answer by Chris T. K Monday, March 28, 2011 12:16 PM
    • Unproposed as answer by Chris T. K Monday, March 28, 2011 12:16 PM
    Friday, February 11, 2011 3:43 PM
  • Hey Daniel,

     

    Not sure if you found the answer you were looking for, or if you still check this post. but had the EXACT same issue as you. I would get the error:

     

    Incorrect syntax near 'nvarchar'.
    Must declare the scalar variable "@BookID"

     

    I finally got it fixed. It seem to be an issue that SQL Server was having with column names that have a space in it. you can read up on it here:

    http://support.microsoft.com/kb/264687/EN-US/

     

    After I removed the space in my column name, I could "update" from my DataGrid smoothly. I noticed in your codes Update statement that you had a column named " [SB Type]" You may want to change this to "SB_Type" and give it a shot. The rest looks good to me.

     

    Good luck!

    • Proposed as answer by Chris T. K Monday, March 28, 2011 12:24 PM
    Monday, March 28, 2011 12:24 PM
  • @Chris

    Thanks! your solution works. I have lost a day looking for this.

    Peter

    Wednesday, May 16, 2012 2:42 PM