1

I am new to working on vb.net 2008 and SQL Server 2005. I want to insert multiple selected check box values from a checkedlistbox at one click of insert button in vb.net.

Can anyone show me how I can do this?

My code is:

Dim Checkeditems As Integer

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    cmd.CommandText = ("Insert into Modules where Module_Name=@Module_Name")
    cmd.Connection = cn
    cn.Open()
    For Each Checkeditem In CheckedListBox1.SelectedValue.ToString
        cmd.Parameters.AddWithValue("@Module_Name", Checkeditem)
    Next
    MsgBox("inserted", MsgBoxStyle.OkOnly)
    cn.Close()
End Sub

This is how I fill the CheckedListBox

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 = ""
Improve this question

2 Answers 2

Reset to default
2

the first thing you must correct is that their is no where condition is allowed with Insert command.

If you want to insert multiple rows in a single query means you con refer this link or try the following,

Dim sql as String="Insert into Modules (Module_Nam) VALUES "
For Each itemChecked In CheckedListBox1.CheckedItems
    sql & = "('" &  itemChecked.ToString & "'),"
Next
cmd.CommandText = sql.SubString(0,Len(sql)-1) '<-- avoid an additional comma
cmd.Connection = cn
cn.Open()
cmd.ExecuteNonQuery()
MsgBox("inserted", MsgBoxStyle.OkOnly)
cn.Close()
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Semicorrect. A part from the part about MULTIPLE SELECTED CHECKBOXES
CheckedListBox.CheckedIndices, and now it is worse because the Items is not a collection of checkboxes.
hello Neethu Soman your code is helping me but its showing an error that is Incorrect syntax near 'System.Data.DataRowView' on (cmd.ExecuteNonQuery)
my code Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim sql As String = "Insert into Modules (Module_Name) VALUES" For Each itemChecked In CheckedListBox1.CheckedItems sql &= "('" & itemChecked.ToString & "')" Next cmd.CommandText = sql.Substring(0, Len(sql) - 1) '<-- avoid an additional comma cmd.Connection = cn cn.Open() cmd.ExecuteNonQuery() MsgBox("inserted", MsgBoxStyle.OkOnly) cn.Close()
1

The answer from Neethu Soman is steering you in the right direction but contains some errors and some inefficiencies.

First point: if you have many elements in your checklist it is better to use a StringBuilder class to avoid too many string concatenations.
Second point: you should loop over the CheckedIndices property to retrieve the Item text to insert avoiding an unnecessary loop over all the items. 

Dim sql = new StringBuilder("Insert into Modules (Module_Nam) VALUES ")
For Each c In CheckedListBox1.CheckedIndices
    Dim value = CheckedListBox1.Items(c).ToString
    sql.Append("('" &  value & "'),"
Next
sql.Length -= 1 ' Remove the last comma '
cmd.CommandText = sql.ToString()
cmd.Connection = cn
cn.Open()
Dim rowsInserted = cmd.ExecuteNonQuery()
MsgBox("inserted " & rowsInserted & " rows", MsgBoxStyle.OkOnly)
cn.Close()

EDIT

From your comment below then every Item in the CheckedListBox is a DataRowView and when you want to get back the values checked you need to use a DataRowView object 

Dim sql = 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 & "'),"
Next
sql.Length -= 1 ' Remove the last comma '
cmd.CommandText = sql.ToString()
cmd.Connection = cn
cn.Open()
Dim rowsInserted = cmd.ExecuteNonQuery()
MsgBox("inserted " & rowsInserted & " rows", MsgBoxStyle.OkOnly)
cn.Close()

This will fix the error in reading back the checked values, but I am a bit perplexed by your code. You have a table named Modules from wich you read the id and the name of the modules. Then you write code that INSERT again the Module name in the same table? You end up with duplicate module names in that table (if the Module_Name is not an unique index in that table). Is this really what you are trying to do?

Improve this answer

19 Comments

and if i am using this code then first tell me how i am using two clases at a time
What do you mean with two clases at a time? This condition is not present in your original question
My code is this please check it here is showing arror on the Dim Builder As New System.Text.StringBuilder on Builder type that error is : Error 1 Overload resolution failed because no accessible 'New' can be called without a narrowing conversion: 'Public Sub New(value As String)': Argument matching parameter 'value' narrows from 'Object' to 'String'. 'Public Sub New(capacity As Integer)': Argument matching parameter 'capacity' narrows from 'Object' to 'Integer'. and my code is : on next comment
Private Sub StringBuilder() Dim Builder As New System.Text.StringBuilder("Insert into Modules (Module_Name) VALUES('" & CheckedListBox1.SelectedItem & "')") For Each c In CheckedListBox1.CheckedItems Dim value = CheckedListBox1.Items(c) Builder.Append("Module_Name ('" & CheckedListBox1.SelectedValue & "'") Next Builder.Length -= 1 ' Remove the last comma ' cmd.CommandText = Builder.ToString() cmd.Connection = cn
Of course, you have changed the code posted and now it makes no sense. SelectedItem is a property of type object, you need a ToString when passing to the StringBuilder constructor. But why you changed the code in that way? Now you don't have anymore the comma required to pass multiple inserts, and the sql produced is not valid.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.