Click here to Skip to main content
15,886,519 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello i am new to working on vb.net 2008, want to know how to insert multiple selected checkboxes value from checkedlistbox in vb.net 2008 to sql server 2005 database. so i have two table in whiche fields name are same such as (Module_ID,Module_Name) and Tables name are 1st one(Modules), 2nd (ruff_Module)... so in First table add some subjects name with number like (1,Math and so on) and second table is empty ... now my question is that i want to add these selected modules from table one which are load into checkedlistbox control , when i select multiple checkboxes they insert to 2nd table just one click on insert button.... please help me out.... MY CODE IS :
VB
Imports System.Data.SqlClient
Imports System.Text
Public Class Form1
    Dim cn As New SqlConnection("Data Source=NIDA-PC\SQLEXPRESS;Initial Catalog=Exam_Data;Integrated Security=True")
    Dim da As New SqlDataAdapter()
    Dim ds As New DataSet
    Dim dr As SqlDataReader
    Dim qry As String
    Dim cmd As New SqlCommand
    Dim qry1 As String
    Dim qry2 As String
    Dim dt As New DataTable
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        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()
        Try
            Dim sql As New StringBuilder("Insert into Modules (Module_Name) VALUES ")
            For Each c In CheckedListBox1.CheckedIndices
                Dim rowView As DataRowView = CheckedListBox1.Items(c)
                Dim value = rowView("Module_Name").ToString()
                sql.Append("('" & value & "')")
                sql.Length -= 1
                cmd.CommandText = sql.ToString()
                cmd.Connection = cn
                cn.Open()
                Dim rowsInserted = cmd.ExecuteNonQuery()
                MsgBox("inserted " & rowsInserted & "rows", MsgBoxStyle.OkOnly)
            Next



        Catch ex As Exception
            MsgBox("Error", MsgBoxStyle.Critical)
        End Try
        cn.Close()
    End Sub

    Stringbuilder()
    End Sub
End Class

and there is showing one error and the error is : Incorrect syntax near 'Math'.

Math is the module name which is load from ist table into checkedlistbox.....
Posted
Updated 14-Nov-14 23:31pm
v2

1 solution

VB
Imports System.Data.SqlClient
Imports System.Text
Public Class Form1
Dim cn As New SqlConnection("Data Source=NIDA-PC\SQLEXPRESS;Initial Catalog=Exam_Data;Integrated Security=True")
Dim da As New SqlDataAdapter()
Dim ds As New DataSet
Dim dr As SqlDataReader
Dim qry As String
Dim cmd As New SqlCommand
Dim qry1 As String
Dim qry2 As String
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
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()
Try
Dim sql As New StringBuilder("Insert into Modules (Module_Name) VALUES ")
For i=0 to CheckedListBox1.items.count-1
if CheckedListBox1.getitemchecked(i)=true then
cmd.CommandText = "insert into ruff_modules values(CheckedListBox1.items(i).item(0),CheckedListBox1.items(i).item(1))
cmd.Connection = cn
cn.Open()
 cmd.ExecuteNonQuery()
MsgBox("inserted " & rowsInserted & "rows", MsgBoxStyle.OkOnly)
Next
 

 
Catch ex As Exception
MsgBox("Error", MsgBoxStyle.Critical)
End Try
cn.Close()
End Sub
 
Stringbuilder()
End Sub
End Class
 
Share this answer
 
v2
Comments
Member 11235240 17-Nov-14 0:23am    
hello king fisher thanx for help me but it generate an error and the error is ::The name "i" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Member 11226817 17-Nov-14 0:28am    
Please Define i as int
like
dim i as interger
Member 11235240 17-Nov-14 0:41am    
i have define this like Dim i as Integer... but this error is generating again and again
Member 11235240 17-Nov-14 23:44pm    
plz help me out

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