CODEWITHSUNDEEP
c#sqlsql-serverinsertsql-server-ce
Skoder
Skoder

Reputation: 4053

Simple SQL Insert

I'm new to databases and am trying to add a new record using SQL. The code runs fine the first time, but the second time throws an error saying that it can't write duplicates to a unique key. The third time runs fine, but the fourth time throws the error. Basically, it seems that every other time, the error is thrown. I understand why the error would be thrown if data was written, but when I examine the database, it remains empty. What am I doing wrong in my code that is causing the query to not bother writing the data?

EDIT If I enter the SQL directly within the database, it works. It doesn't work when I use the C# code below.

using (SqlCeConnection con = new SqlCeConnection(conString))
{
    con.Open();
    using (SqlCeCommand com = new SqlCeCommand("INSERT INTO User (Name, Age, URL) VALUES ( @name, @age, @url )", con))
    {
        com.Parameters.AddWithValue("@name", "James Y");
        com.Parameters.AddWithValue("@age", 28);
        com.Parameters.AddWithValue("@url", "www.example.com" );
        com.ExecuteNonQuery();
    }
}

Upvotes: 1

Views: 896

Answers (4)

Skoder
Skoder

Reputation: 4053

I've partially figured it out. Apparently you have to also download SQL CE Tools for Visual Studio. I did this and I had a new option to include SQL CE 4 into my project (I was using the SQLCE option, assuming that it would use 4.0 by default since that's the one I installed). Only problem now is that when I try and add it, it says that it's not supported by the project type (Console project). I saw a post on MSDN that said that SQLCE 4 was for web-only projects but it was a post from a few months back and the current download page says it's for web or desktop applications. Either way, this is proving to be too much of a hassle to bother with and so I'm just going to look for an alternate database if I can't resolve this soon.

FIXED I uninstalled SQLCE4 and the SQLCE Tools, then reinstalled them.

Upvotes: 1

ErikEJ
ErikEJ

Reputation: 41819

Since you use DataDirectory in your connection string, the file is copied to your bin/debug folder, so you have several copies of the database, one in your project and one in your debug folder. Make the connection string a full path to avoid any confusion while debugging!

Upvotes: 0

xQbert
xQbert

Reputation: 35343

Name is a reserved word. Wrap it in []

using (SqlCeCommand com = new SqlCeCommand("INSERT INTO User ([Name], Age, URL) VALUES ( @name, @age, @url )", con))

Upvotes: 0

Graymatter
Graymatter

Reputation: 6587

Try and do the following as your SQL:

INSERT INTO [User] (Name, Age, URL) VALUES ( @name, @age, @url )

User is a reserved word in sql server. You should try not to name your table as "user".

Upvotes: 0

Related Questions