c# - 从 Windows 窗体插入连接 SQL Server

Question

我想在我的 RDV 表中插入一条记录。

我的表 RDV 的查询创建:

CREATE TABLE [dbo].[RDV] 
(
    [idRdv]       INT           NOT NULL,
    [objet]       NVARCHAR (50) NULL,
    [objectif]    NVARCHAR (50) NULL,
    [DateRdv]     DATETIME      NULL,
    [commentaire] NVARCHAR (50) NULL,
    [archive]     NVARCHAR (50) NULL,
    [idClient]    INT           NULL,
    [idUser]      INT           NULL,
    [idResultat]  INT           NULL,

    CONSTRAINT [PK_RDV] 
       PRIMARY KEY CLUSTERED ([idRdv] ASC),
    FOREIGN KEY ([idClient]) 
       REFERENCES [dbo].[Client] ([idClient]),
    FOREIGN KEY ([idUser]) 
       REFERENCES [dbo].[User] ([idUser]),
    FOREIGN KEY ([idResultat]) 
       REFERENCES [dbo].[Resultat] ([idResultat]
)

下面是我要插入到 RDV 表中的代码:

private void button1_Click(object sender, EventArgs e)
{
    string cmdStr = "Insert into RDV(idUser, idClient, objet, objectif, DateRdv, commentaire) select (@idUser, @idClient, @objet, @objectif, @DateRdv, @commentaire) from RDV Client User where RDV.idClient = idClient.Client and RDV.idUser = User.idUser ";

    SqlConnection con = new SqlConnection("Data Source=YOSRA-PC\\SQLEXPRESS;Initial Catalog=timar;Integrated Security=True");

    SqlCommand cmd = new SqlCommand(cmdStr, con);
    cmd.Parameters.AddWithValue("@idUser", comboBox1.SelectedValue);
    cmd.Parameters.AddWithValue("@idClient", comboBox2.SelectedValue);
    cmd.Parameters.AddWithValue("@objet", textBox1.Text);
    cmd.Parameters.AddWithValue("@objectif", textBox2.Text);
    cmd.Parameters.AddWithValue("@DateRdv", dateTimePicker1.Value.ToString());
    cmd.Parameters.AddWithValue("@commentaire", textBox4.Text);

    con.Open();
    int LA = cmd.ExecuteNonQuery();
    Console.WriteLine("Ligne ajoutée: {0}", LA);
}

但是它告诉我一个错误:

Incorrect Syntax near ','

我不明白这里有什么问题？

请帮帮我。

Answer 1

string cmdStr = "Insert into RDV(idUser,idClient,objet,objectif,DateRdv,commentaire) select (@idUser,@idClient,@objet,@objectif,@DateRdv,@commentaire) from RDV Client User where RDV.idClient=idClient.Client and RDV.idUser=User.idUser ";

上面一行有很多问题。

1. 表格 RDV Client User 之间缺少逗号，应该是 RDV, Client, User
2. idClient.Client 没有表为idClient，应该是Client.idClient
3. User是保留关键字，与[]一起使用，应该是[User]
4. 避免在 SELECT block 中使用 (, )，这会导致 Incorrect Syntax to ',' 错误.

所以你的代码将是:

string cmdStr = "Insert into RDV (idUser,idClient,objet,objectif,DateRdv,commentaire) select @idUser,@idClient,@objet,@objectif,@DateRdv,@commentaire from RDV, Client, [User] where RDV.idClient = Client.idClient and RDV.idUser = [User].idUser ";

---

更新:

上面相同的代码可以用 JOIN 编写如下:

string cmdStr = "INSERT INTO RDV (idUser, idClient, objet, objectif, DateRdv, commentaire) " +
                "SELECT @idUser, @idClient, @objet, @objectif, @DateRdv, @commentaire " +
                "FROM RDV R " +
                "JOIN Client C ON C.idClient = R.idClient " +
                "JOIN [User] U ON U.idUser = R.idUser ";