CODEWITHSUNDEEP
asp.netsqlgridview
Jean-François Côté
Jean-François Côté

Reputation: 4340

Incorrect syntax near 'nvarchar' in update ASP.NET Gridview

Very simple question but all the answer I read over the web doesn't apply.

I try to do an update on a ASP.NET Gridview but when I click on update, I get this error:

Incorrect Syntax near 'nvarchar'. The scalar variable @intID must be declare.

Here is my datasource. I guess the problem come from here but I can't see where...

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:connexionDev %>"  
        DeleteCommand="DELETE FROM [tbl_Bug] WHERE intID = @intID" 
        SelectCommand="SELECT [intID],[strTitre],[strDescription],[intStatus_FK],[intType_FK],[intSeriousness_FK] FROM [tbl_Bug]"  
        UpdateCommand="UPDATE [tbl_Bug] SET [strTitre] = @strTitre ,[strDescription] = @strDescription ,[intStatus_FK] = @intStatus_FK ,[intType_FK] = @intType_FK ,[intSeriousness_FK] = @intSeriousness_FK WHERE [intID] = @intID">   
        <DeleteParameters>   
            <asp:Parameter Name="intID" Type="Int32" />   
        </DeleteParameters>   
        <UpdateParameters>
            <asp:Parameter Name="intID" Type="Int32" />
            <asp:Parameter Name="strTitre" Type="String" />   
            <asp:Parameter Name="strDescription" Type="String" />
            <asp:Parameter Name="intStatus_FK" Type="Int32" />   
            <asp:Parameter Name="intType_FK" Type="Int32" />
            <asp:Parameter Name="intSeriousness_FK" Type="Int32" />   
        </UpdateParameters>     
    </asp:SqlDataSource>

Thanks for your help in advance!

EDIT - EDIT - EDIT

Well, I wanted to use SQL Profiler but it seems that it's not in my version (SQL server 2008 Express) so I tried another sql profiler that is open source but I never understood how it worked and it was always crashing ...

Is there any other way to know the query that are used so I can track my problem?

Upvotes: 3

Views: 11524

Answers (5)

Akif
Akif

Reputation: 29

This is always the case when you have spaces in your field/column names - rename the DB columns and remove spaces - it will work .

If you look at the SQL server monitor and inspect the generated SQL code for Update - it is messed up due to spaces in column names.

Upvotes: 0

Jean-Fran&#231;ois C&#244;t&#233;
Jean-Fran&#231;ois C&#244;t&#233;

Reputation: 4340

Hi everyone and thanks for your help

My query was good, it was the binding in the GridView that was bad, for a very very simple detail

This will work: Text='<%# Bind("myValue") %>'

While this wont: Text='<%# Bind("[myValue]") %>'

So watch out! :) Now everything is working!

Thanks again!

Upvotes: 4

DmitryK
DmitryK

Reputation: 5582

I think you need <SelectParameters>...</SelectParameters> section where you declare all your fields.

Upvotes: 0

Pavel Minaev
Pavel Minaev

Reputation: 101565

You can register an event handler for SqlDataSource.Updating event - it will be raised when the command is prepared, and before executing it. The command will be a DbCommand object, which can be retrieved from an instance of SqlDataSourceCommandEventArgs that your event handler will get via its Command property. Dump both the text, and the actual names and values of all command parameters (in DbCommand.Parameters collection), and check it for errors.

Upvotes: 0

Timothy Walters
Timothy Walters

Reputation: 16874

In cases like this the answer is often to run SQL Profiler and see what SQL is being sent, it's often a mismatch in variable names or something equally simple once you see what being sent to your SQL Server.

Upvotes: 1

Related Questions