Member Avatar for amy 86

hi dears..
i have really big problem with save values of checkedlistbox in one field but i can't do it..what can i do??
is it a database or code problem??
i want the data save like this: 1;2;3 for example..is that possible??

plzzz help..

Edited by amy 86
  • 3 Contributors
  • 7 Replies
  • 404 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by amy 86

Dani AI

Generated

Summary of practical options for (and nod to and ): you can (A) store the checked IDs as one semicolon-delimited text field (easy, quick), (B) store one row per selection in a junction table (recommended, query-friendly), or (C) use a bitmask/integer (compact but fragile). Below are concise, safe VB.NET examples and key troubleshooting notes so you can pick the right approach.

Semicolon list (simple). build a list of checked item IDs/text, join with ;, then insert with a parameterized OleDb command (do not concatenate SQL):

Dim ids As New List(Of String)
For i As Integer = 0 To CheckedListBox1.Items.Count - 1
    If CheckedListBox1.GetItemChecked(i) Then
        Dim item = CheckedListBox1.Items(i)
        Dim id As String = If(TypeOf item Is DataRowView, DirectCast(item, DataRowView)("ID").ToString(), item.ToString())
        ids.Add(id)
    End If
Next

Dim joined As String = String.Join(";", ids)

Using conn As New OleDbConnection(connectionString)
  Using cmd As New OleDbCommand("INSERT INTO Test ([Num],[Q3_2c]) VALUES (?,?)", conn)
    cmd.Parameters.AddWithValue("Num", CInt(No.Text))
    cmd.Parameters.AddWithValue("Q3_2c", joined)
    conn.Open()
    cmd.ExecuteNonQuery()
  End Using
End Using

Normalized (recommended). create table TestSelections(Num, SelectionID) and insert one row per checked item — easier to query and maintain:

Using conn As New OleDbConnection(connectionString)
  conn.Open()
  Using cmd As New OleDbCommand("INSERT INTO TestSelections ([Num],[SelectionID]) VALUES (?,?)", conn)
    For Each sid In ids
      cmd.Parameters.Clear()
      cmd.Parameters.AddWithValue("Num", CInt(No.Text))
      cmd.Parameters.AddWithValue("SelectionID", CInt(sid))
      cmd.ExecuteNonQuery()
    Next
  End Using
End Using

Bitmask (advanced). build a single integer where each bit represents an option:

Dim mask As Integer = 0
For i As Integer = 0 To CheckedListBox1.Items.Count - 1
  If CheckedListBox1.GetItemChecked(i) Then mask = mask Or (1 << i)
Next
' store mask as numeric; to test: If (mask And (1 << i)) <> 0 Then ...

Troubleshooting and cautions: avoid CommandType.TableDirect here, don’t build SQL with string concatenation, use GetItemChecked rather than indexing CheckedItems incorrectly (that causes the exception in your sample), and always use Using/close connections. If you keep the semicolon format, search reliably with Access’s InStr(';' & [Q3_2c] & ';',';2;')>0. For long-term maintainability choose the normalized table approach.

Recommended Answers

All 7 Replies

Member Avatar for G_Waddell

Hi,

I can't tell until I see your code...

Is it you want to store which checkboxes the person has checked them?

Edited by G_Waddell
Member Avatar for amy 86

hi..thanks for reply..yes i want to store the checked box by user which may be more than one value but i need them in one field as i explained previously..

here is sample from code:

 Private Sub confirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles confirm.Click
        Dim cmd As New OleDbCommand
        Dim y As New OleDbParameter("Num", OleDbType.Integer)
        Dim x As New OleDbParameter("Q3_2c", OleDbType.Integer)
        Dim i, j As New Integer

        Try
            conn.Open()
            J = 0

            For i = 0 To CheckedListBox1.Items.Count - 1

                If CheckedListBox1.CheckedItems(i) = True Then

                    J = J + 1

                End If

                For Each check In CheckedListBox1.CheckedItems(i)

                    cmd.CommandText = "INSERT INTO Test([Num],[Q3_2c])VALUES(" + No.Text + "," + check + ")"
                    cmd.CommandType = CommandType.TableDirect
                    cmd.Connection = conn
                      Next
                cmd.ExecuteNonQuery()
            Next
            cmd.ExecuteNonQuery()
            MsgBox("Done..")
                 conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        End Sub
Edited by amy 86
Member Avatar for ponkhiraj

Try to transfer your checkedlistbox item to Textbox, then you may insert it to Access or others. Hope you got the Idea.

Member Avatar for amy 86

thanks for reply..but i can't do this because i have more than 50 values for this list and user isn't able to check with out choose a case whiyh i already determine befor this step..hope you got ma idea..

Member Avatar for G_Waddell

Hmmm....

There are are a couple of ways you can do this, seeing as you only have one field to store it in and you could loop through your items as you are doing and if the item.checked then append it to a string that you then store in the DB field. This is nasty though when it comes to reading back.. as you will have to parse through a long list of delimited text to see what the user clicked.

If you understand bitwise logic you could store the whole thing as a single numeric field and use comparisons to see which checkboxes were checked. I've done this before for storing a users options on a web site but it was ages ago and I can't quite remember how I did it.

I think I assigned a numeric ID to each option then when the user selected it I used an OR to store it in my database field i.e. NewUserValue = OldUserValue OR SelectedOptionID

Then when it came to to see if the user had checked the option, I used an AND like so
IF UsersSelectedOption AND UsersOption = UsersOption then - option is selected

Member Avatar for G_Waddell

Hi

I think your values would have to be in the order 1,2,4,8,16,32,64,128,256 etc

i.e. if I used a value 3 then SelectedOptions AND 2 = 2 would be true as 3 = 11 and 2 = 10

Member Avatar for amy 86

thanks dear 4 reply..but i didn't get it..am sorry..am really new in this..could you please show me how can i fix my code to perform your idea??

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.