CODEWITHSUNDEEP
sql-servervb.netsql-server-2005visual-studio-2008
mano
mano

Reputation: 11

Insert >1 selected checkbox values from CheckedListBox into SQL Server

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 = ""

Upvotes: 1

Views: 2784

Answers (2)

Steve
Steve

Reputation: 216243

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?

Upvotes: 1

user3972104
user3972104

Reputation:

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()

Upvotes: 2

Related Questions