SQL-CE database requires to many inputs

Question

0.00/5 (No votes)

See more:

Hi I have just migrated from MS SQL to MS SQL-CE, now I have this problem DB requieres to many inpust, I didnt have this problem before. Here is code which i am using

db.Execute("INSERT INTO UserProfile   VALUES (@0,'0',@1,'0','','0')", name, email);

and here is table definition

SQL

CREATE TABLE UserProfile 
(UserId bigint NOT NULL IDENTITY(1,1) PRIMARY KEY,
[User] nvarchar(50) NOT NULL,
[Group] smallint,
Email nvarchar(50),
TipsByEmail smallint,
Phone nvarchar(50),
TipsBySMS smallint);

and here is error

Server Error in '/' Application.
The number of columns in the query and the table must match. [ Number of columns in query = 6, Number of columns in table = 7 ]
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.SqlServerCe.SqlCeException: The number of columns in the query and the table must match. [ Number of columns in query = 6, Number of columns in table = 7 ]

Source Error:


Line 35:     if (!user)
Line 36:       {
Line 37:       db.Execute("INSERT INTO UserProfile   VALUES (@0,'0',@1,'0','','0')", name, email);
Line 38:       WebSecurity.CreateAccount(name, password, false);
Line 39:       WebSecurity.Login(name,password,true);


Source File: h:\root\home\pedro007-001\www\site\Register.cshtml    Line: 37

Stack Trace:


[SqlCeException (0x80004005): The number of columns in the query and the table must match. [ Number of columns in query = 6, Number of columns in table = 7 ]]
   System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) +48
   System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() +700
   System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) +542
   System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() +21
   WebMatrix.Data.Database.Execute(String commandText, Object[] args) +111
   ASP._Page_Register_cshtml.Execute() in h:\root\home\pedro007-001\www\site\Register.cshtml:37
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +199
   System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +69
   System.Web.WebPages.WebPage.ExecutePageHierarchy() +131
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +78
   System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContextBase httpContext) +116


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

Posted 11-Aug-13 5:55am

wirec aaht

Add a Solution

Comments

[no name] 11-Aug-13 11:58am

"The number of columns in the query and the table must match" is perfectly clear. You either need to add the columns to insert values into to your query or add another variable to your query to make the number of variables and columns match.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-08-11T06:02:00

Solution 1

You need to get into the habit of naming your columns when you do an INSERT operation: because you have an IDENTITY value as the first column, you need to explicitly skip it and start inserting from the second:

SQL

INSERT INTO UserProfile ([User], [Group], Email, TipsByEmail, Phone, TipsBySMS)  VALUES (@0,'0',@1,'0','','0')

Should fix it.

Posted 11-Aug-13 6:02am

OriginalGriff

Comments

wirec aaht 11-Aug-13 12:46pm

thanks really works, but why did it work before without skiping first column?

OriginalGriff 11-Aug-13 14:31pm

I don't know - I can't see the SQL table data from here, but is it possible that either you had the columns in a different order, or you did name the columns - I notice a double space in your original SQL where they would have gone.