从一个部门插入多个id

本文关键字：插入 id 一个 | 更新日期: 2023-09-27 18:06:26

我正在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等于第二个表的id jos_users

so 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();
        }
    }

从一个部门插入多个id

尝试运行:

Insert into jos_users_quizzes(quiz_id, user_id) select (your_quiz_id, user_id) from   jos_users where department='selectd_depatment'
相关文章：