Insert to a SQL Server CE database

Question

How do you insert into a table in a .sdf database?

I've tried the following:

string connection = @"Data Source=|DataDirectory|\InvoiceDatabase.sdf";
SqlCeConnection cn = new SqlCeConnection(connection);

try
{
   cn.Open();
}
catch (SqlCeException ex)
{
    MessageBox.Show("Connection failed");
    MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
    Application.ExitThread();
}

string clientName = txt_ClientName.Text;
string address = txt_ClientAddress.Text;
string postcode = txt_postcode.Text;
string telNo = txt_TelNo.Text;

string sqlquery = ("INSERT INTO Client (Name,Address,Postcode,Telephone_Number)Values(" + clientName + "','" + address + "','" + postcode + "','" + telNo + ")");
SqlCeCommand cmd = new SqlCeCommand(sqlquery, cn);

try {
  int affectedRows = cmd.ExecuteNonQuery();

  if (affectedRows > 0)
  {
     txt_ClientAddress.Text = "";
     txt_ClientName.Text = "";
     txt_postcode.Text = "";
     txt_TelNo.Text = "";
     MessageBox.Show("Client: " + clientName + " added to database. WOoo");
  }
}
catch(Exception){
    MessageBox.Show("Insert Failed.");
}

But it doesn't seem to matter what i do it just shows "Insert Failed".

Thanks in advance.

Please catch the concret exception and post the message here — Tomtom, Mar 27 '13 at 13:33

Anri · Answer 1 · 2013-03-27T13:40:14.570

2

You forgot opening quotation mark on the first value.

Values(" + clientName + "','"

change to:

Values('" + clientName + "','"

But this is generally a bad way to build query. Use parametrized query instead.
See: http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand.parameters(v=vs.80).aspx

catch(Exception ex)
{
   MessageBox.Show(ex);
}

Will give you more info on error.

edited Mar 27 '13 at 13:40

answered Mar 27 '13 at 13:33

Anri

6,175
3
37
61

score 2 · Answer 2 · edited May 23 '17 at 12:06

It is the same old story. When you build a sql command concatenating string these kinds of errors abund. And the simple syntax problem is not the worst. The Sql Injection is the most dangerous one.

Please build your query in this way

string sqlquery = ("INSERT INTO Client (Name,Address,Postcode,Telephone_Number)" + 
                   "Values(@client,@address, @postcode, @tel)";
SqlCeCommand cmd = new SqlCeCommand(sqlquery, cn);
cmd.Parameters.AddWithValue("@client", clientName);
cmd.Parameters.AddWithValue("@address", address);
cmd.Parameters.AddWithValue("@postcode", postcode);
cmd.Parameters.AddWithValue("@tel", telNo);
cmd.ExecuteNonQuery();

As others have already said your syntax error is caused by omitting the initial single quote. But you could have other errors. For example, what about a client called O'Hara?. Now you have a single quote inside the clientname and this wreak havoc your string concatenation. Instead a parameter will be accurately parsed and every problematic character found will be treated appropriately (in this case doubling the single quote)

+1000000 if I could - couldn't agree more; if anyone today is still teaching students to concatenate together their SQL statement - he should be tickled to death with a feather..... — marc_s, Mar 27 '13 at 13:44

score 1 · Answer 3 · edited Mar 27 '13 at 13:38

1

Your SQL statement is incorrect.

string sqlquery = ("INSERT INTO Client (Name,Address,Postcode,Telephone_Number)Values('" + clientName + "','" + address + "','" + postcode + "','" + telNo + "')");

Take this. You forgot the ' at the beginning and the end of the values

edited Mar 27 '13 at 13:38

marc_s

732,580
175
1,330
1,459

answered Mar 27 '13 at 13:35

Tomtom

9,087
7
52
95

score 0 · Answer 4 · answered Mar 27 '15 at 05:02

To insert data into Sql, data type should be considered. If you insert a string value (varchar) you have to surround it by single quotation, like '"+full_Name+"', but integer type doesn't need this. example

string myQuery = "INSERT INTO Persons (phone, fullname) VALUES ("+telNo+",'"+full_Name+"')";

where full name is string variable and phone number is only number.

Insert to a SQL Server CE database

4 Answers4

Linked