插入到SQL Server CE数据库 [英] Insert to a SQL Server CE database

问题描述

如何插入.sdf数据库的表中?

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

我尝试了以下操作:

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".

谢谢.

推荐答案

这是一个古老的故事.当您建立连接字符串的sql命令时，这些错误种类很多.而且简单的语法问题也不是最坏的. SQL注入是最危险的.

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.

请以这种方式构建查询

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();

正如其他人已经说过的那样，您的语法错误是由于省略了最初的单引号引起的.但是您可能还有其他错误.例如，名为O'Hara的客户端呢?现在，您在客户名中使用了一个单引号，而这种麻烦破坏了您的字符串连接. 取而代之的是，将准确地解析参数，并适当地处理找到的每个有问题的字符(在这种情况下，将单引号加倍)

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)

这篇关于插入到SQL Server CE数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！