参数化查询INSERT INTO语句中的语法错误

Question

我正在编写一个带有.NET和MS的桌面软件，但是当我创建插入数据的代码时，它会抛出一个异常，我尝试在任何地方搜索，但仍然没有修复。

下面是我的所有专栏(忽略不同的语言)：

NomorNota (AutoNumber, PrimaryKey), Tanggal (Date/Time),
NamaPelanggan (Long Text), Alamat (Long Text), NoHP(Long Text),
TipeHP (Long Text), Keluhan (Long Text), Kerusakan (Long Text),
KondisiHP (Long Text), Kelengkapan (Long Text), Warna (Long Text),
KataSandi/Pola (Number), DP (Currency), Teknisi (Long Text), Status (Long Text), 
TanggalKonfirmasi (Date/Time), IsiKonfirmasi (Long Text),
Biaya (Currency), TambahanBiaya (Currency), TotalBiaya (Currency),
HargaSparepart (Currency), Laba/Rugi (Currency)

这是我的c#代码：

private void btnSave_Click(object sender, EventArgs e)
{
    OleDbConnection conn = new OleDbConnection();
    conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source=Databases/database.accdb;
                Persist Security Info=False";

    try
    {
        OleDbCommand cmd = conn.CreateCommand();
        conn.Open();

        string kelengkapan = "";
        string status = "";

        int totalBiaya = int.Parse(txtBiaya.Text) + int.Parse(txtTambahanBiaya.Text);
        int labaRugi = totalBiaya - int.Parse(txtHargaSparepart.Text);

        if (chckSIM.Checked)
            kelengkapan += "SIM ";

        if (chckMemory.Checked)
            kelengkapan += "Memory ";

        if (chckKondom.Checked)
            kelengkapan += "Kondom ";

        if (chckBaterai.Checked)
            kelengkapan += "Baterai";

        if (comboStatus.SelectedItem != null)
            status = comboStatus.SelectedItem.ToString();
        else
            status = "BELUM DISETTING";

        string cmdText = @"INSERT INTO DataServisan (Tanggal, NamaPelanggan, Alamat,
                    NoHP, TipeHP, Keluhan,
                    Kerusakan, KondisiHP, Kelengkapan,
                    Warna, KataSandi/Pola, DP,
                    Teknisi, Status, TanggalKonfirmasi,
                    IsiKonfirmasi, Biaya, TambahanBiaya,
                    TotalBiaya, HargaSparepart, Laba/Rugi) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        cmd.CommandText = cmdText;

        cmd.Parameters.Add(new OleDbParameter { Value = DateTime.Now.ToString("dd-MM-yyyy") });
        cmd.Parameters.Add(new OleDbParameter { Value = txtNamaPelanggan.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = txtAlamat.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = txtNoHP.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = txtTipeHP.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = txtKeluhan.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = txtKerusakan.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = txtKondisiHP.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = kelengkapan });
        cmd.Parameters.Add(new OleDbParameter { Value = txtWarna.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = int.Parse(txtKataSandiPola.Text) });
        cmd.Parameters.Add(new OleDbParameter { Value = int.Parse(txtDP.Text) });
        cmd.Parameters.Add(new OleDbParameter { Value = txtTeknisi.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = status });
        cmd.Parameters.Add(new OleDbParameter { Value = dateKonfirmasi.Value.ToString("dd-MM-yyyy") });
        cmd.Parameters.Add(new OleDbParameter { Value = txtIsiKonfirmasi.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = int.Parse(txtBiaya.Text) });
        cmd.Parameters.Add(new OleDbParameter { Value = int.Parse(txtTambahanBiaya.Text) });
        cmd.Parameters.Add(new OleDbParameter { Value = totalBiaya });
        cmd.Parameters.Add(new OleDbParameter { Value = txtHargaSparepart.Text });
        cmd.Parameters.Add(new OleDbParameter { Value = labaRugi });

        cmd.ExecuteNonQuery();

        MessageBox.Show("Berhasil!", "Transaksi Servis");

        this.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show("An error has occurred: " + ex);
    }
    finally
    {
        conn.Close();
    }
}

Answer 1

您需要引用其中有斜杠的列，因此在insert语句中更改：

• KataSandi/Pola到[KataSandi/Pola]
• Laba/Rugi到[Laba/Rugi]

您通常应该避免在列名中使用这类字符。它使您的代码更难阅读。当阅读你的思想时，首先可能会认为它是一种分裂，只有这样才会意识到它是一个列名。

在没有显式引用的情况下，解析器也有同样的问题，只不过它抛出了一个语法错误，而没有意识到它毕竟是列名。