多个选定值插入 [英] multiple selected value insert

问题描述

你好我需要帮助知道如何插入多个选定的empl_ID（varchar（50））和mudule_ID（int）.....实际上我有三个数据库sqlserver 2005表...其中一个是模块和第二个一个是分配模块，第三个是员工....在第一个模块中，存储所有模块，如主题（数学，物理，英语......等）字段是Module_ID（int）和Module_Name（varchar（50））..在第二个Assign_Modules（empl_ID（varchar（50）），Module_ID（int）），第三个是雇员（empl_ID（int），empl_Name（varchar（50））......这些是我的三个表....现在关于我的vb.net 2008设计我得到了combobox，其中从employees表加载empl_ID（表示员工姓名）并获得Checkedlistbox，其中从模块表加载所有模块....以及一个用于将多个选定模块插入Asiign_Modules的按钮表格哪些字段是（empl_ID（从组合框中选择），模块ID从选中列表框中选择）我的代码是.....

hello i need help to know how to insert multiple selected empl_ID(varchar(50))and mudule_ID(int)..... actually i have three tables of database sqlserver 2005... in which one is Modules and 2nd one is assign modules, 3rd one is employees .... in 1st one modules, storing all modules like subjects(Maths,Physics,english...etc) fields are Module_ID(int)and Module_Name(varchar(50))... And In 2nd one Assign_Modules(empl_ID(varchar(50)),Module_ID(int)), 3rd one is employees(empl_ID(int),empl_Name(varchar(50))... these are my three tables.... Now about my vb.net 2008 design i get on combobox in which loading empl_ID(means name of employees) from employees table and get Checkedlistbox in which loading all modules from modules table .... and one button for insert multiple selected modules to Asiign_Modules table which fields are (empl_ID(select from combobox) and Module_ID select from checked list box) My code is.....

Imports System.Data.SqlClient
Imports System.Text
Public Class Form5
    Dim cn As New SqlConnection("Data Source=NIDA-PC\SQLEXPRESS;Initial Catalog=Exami_Data;Integrated Security=True")
    Dim da As New SqlDataAdapter()
    Dim ds As New DataSet
    Dim dr As SqlDataReader
    Dim qry As String
    Dim qry1 As String
    'Dim qry2 As String
    Dim dt As New DataTable
    Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        qry1 = "Select empl_ID from employees where User_Type='Instructor'"
        da = New SqlDataAdapter(qry1, cn)
        ds = New DataSet()
        da.Fill(ds, "tab")
        ComboBox1.DataSource = ds.Tables("tab")
        ComboBox1.DisplayMember = "empl_ID"
        ComboBox1.ValueMember = "empl_ID"
        ComboBox1.Text = ""

        qry = "Select Module_ID,Module_Name from Modules"
        da = New SqlDataAdapter(qry, cn)
        ds = New DataSet()
        da.Fill(ds, "tab")
        CheckedListBox1.DataSource = ds.Tables("tab")
        CheckedListBox1.DisplayMember = "Module_Name"
        CheckedListBox1.ValueMember = "Module_ID"
        CheckedListBox1.Text = ""
    End Sub
Public Sub Stringbuilder()
  Dim item As Object
        cn.Open()
        For Each item In CheckedListBox1.CheckedItems
            Dim cmd As New SqlCommand
            cmd.CommandText = "insert into Assign_Modules(empl_ID,Module_ID) values(" & ComboBox1.GetItemText(ComboBox1.SelectedItem.ToString) & "," & item(0).ToString & ")"
            cmd.Connection = cn
            Dim rowsInserted = cmd.ExecuteNonQuery()
            MsgBox("inserted " & rowsInserted & "rows", MsgBoxStyle.OkOnly)
        Next
        cn.Close()
  End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Stringbuilder()
    End Sub
End Class

推荐答案

hi，



据我了解你的问题，你要插入

选定模块的选定用户



插入到Assign_Modules（empl_ID ，Module_ID）

从员工中选择empl_ID，Module_ID，其中Module_ID在（selected_Module_ID）和empl_ID在（selected_empl_ID）

As I understand your problem you want to insert
selected user with selected Modules

insert into Assign_Modules(empl_ID,Module_ID)
Select empl_ID,Module_ID from employees,Modules where Module_ID in (selected_Module_ID ) and empl_ID in (selected_empl_ID)

中的模块

这篇关于多个选定值插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！