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

我想在我的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);
}

但这显示了一个错误：


  '，'附近的语法不正确


我不明白这里出了什么问题？

请帮我。

最佳答案

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

上一行有很多问题。


表RDV Client User之间缺少逗号，应为RDV, Client, User
idClient.Client没有作为idClient的表，应该为Client.idClient
User是保留关键字，与[]一起使用，应为[User]
避免在(块中使用)，SELECT，这会导致不正确的语法为“，”错误。




因此您的代码将是：

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

关于c# - 从Windows窗体插入联接SQL Server，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37121494/