c# - 插入一个部门的多个 ID

Question

我正在 asp.net 上创建一个 Web 表单，该表单将允许最终用户将基于所选部门的多个用户分配给测验。

数据库是mysql数据库，因为我使用joomla

the tables on mysql are:

jos_users_quizzes with the following columns:
id
quiz_id
user_id

I have a second is called called  jos_users with this columns

id
name
username
department

第一个表上的 user_id 将与第二个表 jos_users 的 id 相同

所以 quiz_id = id (jos_users)

如何构建一个查询以将选定部门的 ID 多次插入 jos_users_quizzes 表中...一键单击，我就有一个用于测验 ID 的下拉列表和一个用于用户 ID、部门的下拉列表

我需要从所选部门中选择所有用户 ID 并将这些 ID 插入到 user_quizzes 表中。

来自 ASP.NET 表单的代码以插入...

 private void InsertRecords(StringCollection sc)
    {
        string ConnectionString = @"driver={MySQL ODBC 5.1 Driver};server=localhost;database=db_dhruniversity;uid=root;pwd=;";
        OdbcConnection conn = new OdbcConnection(ConnectionString);

        try
        {

            conn.Open();


            string quizidselected = DropDownList1.SelectedValue;
            string deptselected = ListBox2.SelectedValue;
           // OdbcCommand cmd = new OdbcCommand("INSERT INTO jos_jquarks_users_quizzes (user_id, quiz_id)    SELECT uid, ' " + quizidselected + " ' FROM dhruprofile  WHERE department = ' " + deptselected + " '");             
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();
            Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);

        }

        catch (System.Data.SqlClient.SqlException ex)
        {

            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }

        finally
        {

            conn.Close();

        }

    }

Answer 1

尝试运行:

Insert into jos_users_quizzes(quiz_id, user_id) select (your_quiz_id, user_id) from   jos_users where department='selectd_depatment'