由 C# 生成的双引号进入 SQL 语法

本文关键字：SQL 语法 | 更新日期: 2023-09-27 18:30:27

谁能指出我做错了什么，我在SQL中收到一个错误，说语法错误靠近第1行的"

我已经尝试了一切，似乎无法摆脱它？

这是我正在使用的代码 -

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        MySqlConnection conn;
        MySqlConnection conn1;
        bool connection = false;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.toolStripStatusLabel1.Text = "Initialising";
            db_connect();
        }


        private void db_connect()
        {
            string mydbconn = "server=localhost;user id=root;password=lap;database=test;";
            string mydbconn1 = "server=localhost;user id=root;password=lap;database=test;";

            try
            {
                conn = new MySqlConnection(mydbconn);
                conn1 = new MySqlConnection(mydbconn1);
                conn.Open();
                conn1.Open();
                this.toolStripStatusLabel1.Text = "Connected";
                connection = true;
                if (connection == true)
                {
                    read_data();
                }
            }
            catch (Exception ex)
            {
                this.toolStripStatusLabel1.Text = "WRONG";
                connection = false;
            }
        }


        private void read_data()
        {

            string sql = "SELECT first_name, last_name FROM dan";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string newsql = "REPLACE INTO dan1 (first_name, last_name) values";
                newsql += "(";
                for (int i = 0; i < 2; i++)
                {
                    newsql += reader.GetString(i);
                }
               // System.Console.WriteLine(newsql);

                int res = 0;
                MySqlCommand cmd1 = new MySqlCommand(newsql, conn1);
                try
                {

                    res = cmd1.ExecuteNonQuery();
                    this.richTextBox1.Text = "copying";
                }
                catch (MySqlException ex)
                {
                    this.richTextBox1.Text = ex.Message;
                }
            }

        }
    }
}

由 C# 生成的双引号进入 SQL 语法

您正在创建一个如下所示的查询：

REPLACE INTO dan1 (first_name, last_name) values(JohnDoe

当它应该看起来像这样时;

REPLACE INTO dan1 (first_name, last_name) values ('John', 'Doe')

要像这样动态创建 SQL，您需要正确转义所有字符串数据，正确完成它的方式取决于您使用的数据库。对于MySQL，它将是：

string newsql = "REPLACE INTO dan1 (first_name, last_name) values (";
bool first = true;
for (int i = 0; i < 2; i++) {
  if (first) {
    first = false;
  } else {
    newsql += ",";
  }
  newsql += "'" + reader.GetString(i).Replace("''", "''''").Replace("'", "'''") + "'";
}
newsql += ")";

但是，您宁愿改用参数化查询，这样就不必担心正确设置查询格式和转义字符：

string newsql = "REPLACE INTO dan1 (first_name, last_name) values (@FirstName, @LastName)";
MySqlCommand cmd1 = new MySqlCommand(newsql, conn1);
cmd1.Parameters.Add("@FirstName", reader.GetString(0));
cmd1.Parameters.Add("@LastName", reader.GetString(1));

至少我认为

您的查询可能缺少一个结束括号。您的代码似乎生成如下所示的查询：

REPLACE INTO dan1 (first_name, last_name) values (first_name last_name

缺少结束).我也没有看到添加逗号来分隔值子句中的项目，并且可能项目本身没有被引用。试试这个：

string newsql = "REPLACE INTO dan1 (first_name, last_name) values";
newsql += "(";
for (int i = 0; i < 2; i++)
{
    newsql += reader.GetString(i);
}
newsql += ")";
// System.Console.WriteLine(newsql);