Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.....

VB
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
Posted
Updated 4-Dec-14 20:04pm
v2
Comments
Maciej Los 5-Dec-14 2:05am    
Use proper formatting!
nida jabeen 5-Dec-14 5:32am    
yup
Sinisa Hajnal 5-Dec-14 2:32am    
OK, so what is the problem? The code seems fine at first glance, describe what you're getting (any errors?) and what you're expecting.

Also, why not use something like empl_ID + empl_name for display value?
nida jabeen 5-Dec-14 5:04am    
i am not getting any error but getting wrong result ... i want to insert empl_ID,Module_ID in which empl_ID is the name of employee just like basim and Module_ID is the number of Module just like 1 but the result is in empl_ID store empl_no just like 2 ...

1 solution

hi,

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)
 
Share this answer
 
Comments
nida jabeen 6-Dec-14 0:23am    
thanx its getting name of employees but i want when i select one user and i have for example 3 modules and i want to give only one selected module to insert to that selected employee i want this but your code is give all modules at a time to all employees that is wrong
nida jabeen 6-Dec-14 0:28am    
thank you soo much mudgilsks you are very lucky for me your code your answer very helpful its working thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900