?
Solved

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

Posted on 2007-08-07
12
Medium Priority
?
3,410 Views
Last Modified: 2013-11-26
Hi,
I am using Microsoft Visual Studio 2005 with SQL express 2005 to create a create user page using createuserwizard.
 
I have a problem with inserting paramaters in the table custom_user_profile that i created in ASPNETDB. I am getting the "Incorrect syntax near 'nvarchar'.Must declare the scalar variable "@UserId"." in the last line of the code. (as below). I have tried using breakpoints to see whether the GUID value is in the UserGUID object and also, the paramater has been added in the datasource. Why does the last line not recognize this value?

    protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
    {
        TextBox UserNameTextBox = (TextBox)CreateUserWizardStep2.ContentTemplateContainer.FindControl("UserName");

        SqlDataSource DataSource = (SqlDataSource)CreateUserWizardStep2.ContentTemplateContainer.FindControl("InsertExtraInfo");

        MembershipUser User = Membership.GetUser(UserNameTextBox.Text);

        object UserGUID = User.ProviderUserKey;

        DataSource.InsertParameters.Add(UserId, UserGUID.ToString());

        DataSource.Insert();
    }

The sql datacourse code is as follows:
                    <asp:SqlDataSource ID="InsertExtraInfo" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>"
                        InsertCommand="INSERT INTO Custom_user_profile(UserId, FirstName, MiddleName, LastName, HouseNumber, Address1, Address2, City, Country, Postcode, Role, StudentLevel) VALUES (@UserId, @FirstName, @MiddleName, @LastName, @HouseNumber, @Address1, @Address2, @City, @Country, @Postcode,@Role, @StudentLevel)"
                        ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString.ProviderName %>">
                        <InsertParameters>
                            <asp:ControlParameter ControlID="FirstNameTextBox" Type=string Name="First Name" PropertyName="Text" />
                            <asp:ControlParameter ControlID="MiddleNameTextBox" Type=string Name="Middle Name" PropertyName="Text" />
                            <asp:ControlParameter ControlID="LastNameTextBox" Type=string Name="Last Name" PropertyName="Text" />
                            <asp:ControlParameter ControlID="HouseNumberTextBox" Type=string Name="House Number" PropertyName="Text" />
                            <asp:ControlParameter ControlID="Address1TextBox" Type=string Name="Address1" PropertyName="Text" />
                            <asp:ControlParameter ControlID="Address2TextBox" Type=string Name="Address 2" PropertyName="Text" />
                            <asp:ControlParameter ControlID="CitytextBox" Type=string Name="City" PropertyName="Text" />
                            <asp:ControlParameter ControlID="CountryDropDownList" Type=string Name="Country" PropertyName="SelectedValue" />
                            <asp:ControlParameter ControlID="PostcodeTextBox" Type=string Name="Postcode" PropertyName="Text" />
                            <asp:ControlParameter ControlID="AcademicDropDownList" Type=string Name="Role" PropertyName="SelectedValue" />
                            <asp:ControlParameter ControlID="LevelDropDownList" Type=string Name="StudentLevel" PropertyName="SelectedValue" />
                        </InsertParameters>
                    </asp:SqlDataSource>
                </ContentTemplate>

Have been trying to get this right for 3 days now. :(
0
Comment
Question by:ragsy937
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 19650611
Hi,

INSERT INTO Custom_user_profile(UserId, FirstName, MiddleName, LastName, HouseNumber, Address1, Address2, City, Country, Postcode, Role, StudentLevel) VALUES (@UserId, @FirstName, @MiddleName, @LastName, @HouseNumber, @Address1, @Address2, @City, @Country, @Postcode,@Role, @StudentLevel)

should be

INSERT INTO Custom_user_profile(UserId, FirstName, MiddleName, LastName, HouseNumber, Address1, Address2, City, Country, Postcode, Role, StudentLevel)
select @UserId, @FirstName, @MiddleName, @LastName, @HouseNumber, @Address1, @Address2, @City, @Country, @Postcode,@Role, @StudentLevel

HTH
  David
0
 

Author Comment

by:ragsy937
ID: 19650981
Hi,
Thanx a lot, but i've tried the changes you proposed but it still comes out with the same result. I don't know if this helps, but this is the exception.

[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@UserId".]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3430
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1139
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +495
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +556
   System.Web.UI.WebControls.SqlDataSourceView.Insert(IDictionary values) +29
   System.Web.UI.WebControls.SqlDataSource.Insert() +38
   CreateUser.CreateUserWizard1_CreatedUser(Object sender, EventArgs e) in c:\WebSite Development\OnlineXML\CreateUser.aspx.cs:30
   System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) +96
   System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +422
   System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +199
   System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +651
   System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +158
   System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +39
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +107
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +178
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3839
0
 
LVL 35

Expert Comment

by:David Todd
ID: 19651106
Hi,

I've looked this up in the MSDN Library, there are some good examples there under SqlDataSource control.

I can't tell from your code if this is missing, but the examples do have insert and update templates, and the InsertParameters section is different to your example.

Sorry - not much help here.
  David
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 12

Expert Comment

by:renjurdevan
ID: 19652157
is UserId Identity?

Regards
Renju
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19652827
You have a user id parameter in the SQL, but you don't have a parameter in the SqlDataSource to pass in @UserID.

Bob
0
 

Author Comment

by:ragsy937
ID: 19661224
Hi guys! Thanx lots for your help.

renjurdevan : Yes, UserId (32 character long assigned by the system) is an identity that i retrieve from the ASPNETDB's asp_user table

TheLearnedOne : I thought i added the parameter in the sqldatasource before i called the insert method.

code :

.
.       //before this, the new user has already been created in the asp_users table, now, aim is to add the other fields into the custom_user_profile table which is also in ASPNETDB.
       
        //get all user details from the aspnetDB's asp_users table
        MembershipUser User = Membership.GetUser(UserNameTextBox.Text);
       
        // get UserID from the User that has just been created
        object UserGUID = User.ProviderUserKey;

        //inserts this new userID assigned to this user in the insert Parameter list in SqlDatasource
        DataSource.InsertParameters.Add(UserId, UserGUID.ToString());

        //call insert method, this is where the error is.
        DataSource.Insert();
    }

I have checked using breakpoints and have confirmed that there are 12 parameters (including userID), instead of 11 paramaters (from the newly added fields in a new custome createwizardstep).

Any ideas on why this happened?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19662917
I think it might be because of the timing of things.  Since you didn't define the parameter for @UserID on the control, when the page loads, it expects that parameter, but can't find one.

Bob
0
 

Author Comment

by:ragsy937
ID: 19665868
Thanx learned one,

FUnny thing, i trie experimenting, i deleted the user id column in the sql datbase, and also excluded it from the sql insertcommand. how ever, the same error comes out, but this time,

Incorrect syntax near 'nvarchar'. Must declare the scalar variable "@FirstName". which is the first column of the table.

is this some kind of bug you reckon?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 1500 total points
ID: 19674420
I don't like the SqlDataSource, and I struggle sometimes with its strangeness.  It sure sounds like a problem, but I can't see where it is in your code.  I would work up a small test with a simple SqlDataSource, and see how it works.

Bob
0
 

Author Comment

by:ragsy937
ID: 19675101
thanx lots Bob! appreciate it very much. I have moved on to complete other functional requirements first and then revisit this problem again when new information arises. At the moment, sort of dead end to me. :(
0
 

Author Comment

by:ragsy937
ID: 19687798
Found the solution! i think it must be a bug. Previously, i created a table in the default ASPNETDB database name custom_User_Profile. I have deleted this table and created a new one named UserProfile.

Sounds weird, but the only difference is that one of the table name has underscores. Could this be a bug?

I would like to test this again by creating another table, but dont have time since the deadline is closing. Maybe I'll try afterwards.

Cheerios!
0
 

Expert Comment

by:littlefm022450
ID: 35889734
I am getting this same error with both the UPDATE and INSERT SQL statements and here is the error statement. I have looked all over the internet and can not find a solution. Tried the INSERT statement above and did not help. Here is the error message and stack trace. BTW I am a newbie to this.

[SqlException (0x80131904): Line 1: Incorrect syntax near 'nvarchar'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   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) +175
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +227
   System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +86
   System.Web.UI.WebControls.DetailsView.HandleInsert(String commandArg, Boolean causesValidation) +274
   System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +676
   System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent(Object source, EventArgs e) +113
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question