Insert to a SQL Server CE database

问题:

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.

回答1:

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.

回答2:

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)

回答3:

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

回答4:

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.