insert items from CheckedListBox to table in database

[hhaannyy]

I was very tired of searching, but I did not find anything i have a from and its minted textbox and CheckedListBox the CheckedListBox have five choices and i want to insert the Items(more than 1) Checked from the CheckedListBox and the textbox into a table in the database like this

 con.Open() 
 cmd.Connection = con 
 cmd.CommandText = "insert into users ( user_name,pass_word,premation) VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & CheckedListBox1.Text & "')" 
 cmd.ExecuteNonQuery()

[rproffitt]

That CommandText, is it what you expected when you single stepped your code?

If single stepping doesn't show it, put your CommandText into a string so you can examine it and see what's up.

And let's hope others chime in about allowing text input like that. It can be as funny as Johnny Drop Tables. Longer explanation at https://www.explainxkcd.com/wiki/index.php/Little_Bobby_Tables

[hhaannyy]

this is an example as i want
i ask about insert more than one item from CheckedListBox to table . how can i do this
thank you

[hhaannyy]

this is what i want say add textbox and select hany and hazen them insert this into table

[rproffitt]

Example unclear. Try it again, this time take the content to a string so you can debug it.

Also, you should be concerned about Johnny Drop Tables. Examples? https://www.google.com/#q=sanitize+sql+input+vb.net

[Mr.M]

I personally don't understand the question or problem here. Can you describe more into details as to what you want to achieve, how, and whats/where you currently stand. I see on your code that you want to insert data, but what is the problem exactly?

[Mr.M]

Ow wait a minute. Are you trying to also insert all the data that is on CheckListBox to one column? Is that what you are having problem with?

[hhaannyy]

this is my from when i select hany and select hazem and add anything into the textbox i want to save them in table in my data how can i do this
thank you

[rproffitt]

Again. Change line 3. Make it 2 lines where you put your string together so you can see what's in your command.

Now you can see what's going on.

[Mr.M]

Ok. As of CheckListBox, its a big complicated to tangle it. But this will depend on how you are adding the data from the CheckListBox.

What I mean is that if you want to select more then 1 item and add them to the same column on a database you will need to add them to one variable so that when selecting the second time it won't replace the previously selected item text. Also if you will later on need to retrieve this and set the CheckListBox as per data retrieved, then I think you will need a separator as well. Because you want to select more then one item you can do this.

First thing to do is do a tracing of items by counting them because we get the items by index so double click the CheckListBox and add this code, also Declare Dim myItems As String as global variable so that you will be able to work with it on other controls.

 Dim selectedIndex As Integer = CheckListBox1.SelectedIndex
 If selectedIndex <> -1 Then
 'Loop through items to get their check states.
 For i As Integer = 0 To CheckListBox1.Items.Count -1
 Dim checkstate As CheckState
 checkstate = CheckListBox1.GetItemCheckState(i)
 If (checkstate = checkstate.Checked) Then
 ' Now we know this item is checked so let's add it to our global variable.

 myItems = myItems & CheckListBox1.SelectedItem.ToString
 End If
 Next
 End If

Now you can easily add it to your database.

From your code where you add it database replace CheckedListBox1.Text with myItems.

There you are, try it out and let me know if its helped you or not.
Hope this does answer your question.

[hhaannyy]

thank you for your help but it says
Warning BC42104 Variable 'myItems' is used before it has been assigned a value. A null reference exception could result at runtime.
excuse me i am new at this

[Mr.M]

You need to declare that variable as global, meaning at this part.

 Public Class
 Dim myItems As String
 Private Sub CheckListBox1_SelectedIndexChenge()
 'Add that code I posted first here.
 End Sub

Now on your code before attempting to add the data to the database you need to first verify if its null or not.

 If myItem = "" Then
 'The variable is null/has nothing on it.
 'So don't attempt to insert this
 Else
 'Now this variable has some item so you can now add the insert code to add to database.

[hhaannyy]

this the code

[Mr.M]

Your image has a very low quality. I don't see a thing there. Please copy & past it here it will be easy to read it.

[rproffitt]

Line 7 looks incorrect and a duplicate of your other declaration in line 5.

[hhaannyy]

thank you but i do know how to add it
myitems.what for first selection
and
myitems.what for second selection
finalyThank you so much

[Mr.M]

If you want to save them into same field on a database then you will need to add a delimiter to separate them like John:Sam:Steve

Where in a CheckListBox its
John
Sam
Steve

In any order so if you. So to do so you will need to do something like this myItems = myItems & ":" then when you retrieve it you can then split it on those delimiters so that you can be able to work with each if there's a need.

[hhaannyy]

this my code excuse me again
i cant do it

    Imports System.Data
    Imports System.Data.SqlClient
    Public Class Form1
        Dim cmd As New SqlCommand
        Dim myItems As String
        Private Sub CheckedListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CheckedListBox1.SelectedIndexChanged
            Dim selectedIndex As Integer = CheckedListBox1.SelectedIndex
            If selectedIndex <> -1 Then
                'Loop through items to get their check states.
                For i As Integer = 0 To CheckedListBox1.Items.Count - 1
                    Dim checkstate As CheckState
                    checkstate = CheckedListBox1.GetItemCheckState(i)
                    If (checkstate = CheckState.Checked) Then
                        ' Now we know this item is checked so let's add it to our global variable.
                        myItems = myItems & CheckedListBox1.SelectedItem.ToString
                    End If
                Next
            End If
        End Sub

        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Try
                If myItems = "" Then
                    MsgBox(" ")
                Else
                    con.Open()
                    cmd.Connection = con
                    cmd.CommandText = "insert into test ( name1,name2) VALUES ('" & myItems & "','" & myItems & "')"
                    cmd.ExecuteNonQuery()
                End If
            Catch ex As Exception
                MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            Finally
                con.Close()

            End Try

            MsgBox("saved ok")
        End Sub
    End Class

[Mr.M]

Ow well I notice that on your insert code you want to add each CheckListBox item on its own fields. That can be achieved but not how you are trying.

This is a very simple example which is only limited to 3 as you said you have 3 items on a CheckListBox.

On that global variable, you will need to add other two variables. Or the other better way to do this so that even if you have many items. This method uses Array technique.

But enough with stuff that may confuse you.

You need to add two more variables as global variables.

 Dim myItem2 As String
 Dim myItem3 As String

 'Under the Private Sub CheckListBox_CheckIndexChange()

 'First check if the first variable has something on it.
 If myItems = "" Then
 'Because this is the first time click add what was clicked here.
 myItems = CheckListBox1.SelectedItem.ToString
 Else
 'This variable has something so this means that this is not the first time click so now
 'We need to check our second variable if its has anything on it.
 If myItem2 = "" Then
 'The variable has nothing, that means its now the second time the user click another item so go ahead add data to it.
 myItem2 = CheckListBox1.SelectedItem.ToString
 Else
 'The variable has something so that means now the user has clicked the third item so let's go ahead and add it.
 'First check if it has anything on it.
 If myItem3 = "" Then
 myItem3 = CheckListBox1.SelectedItem.ToString
 End If
 End If
 End If

Also note that you need to properly add this code, its must be inserted inside the first code I gave you. To use this you will simply call what ever variable you want to add to database. But first you will need to verify if they have anything in it.

Also the very same code that you are using can be used its just that you won't be using myItems to add to database but rather first split it. Like this.

This is the sample I'm making to show you how you can also achieve this on this last code you posted.

First this assumes you have used delimiter to separate items in a variable like : so this sample will be using delimiter.

First check if the myItems has a delimiter or not.

 If myItems.Contains(":") Then
 'The variable has delimiter so we need to split to get each item on it.
 Dim m1, m2, m3 As String
 Dim list() As String
 list = myItems.Split(":")
 'Now that we have split the items now let's assign it to appropriate variables.
 'Note that this uses array
 m1 = list(1)
 m2 = list(2)
 m3 = list(3)

 'Now you can add to database using m_ variable for appropriate field.

Not that the first item you selected will be on variable m1 and the second item will be on m2, and so on.

You also need to think out of the box when doing things like this. You can't use the same variable on different field and expect that it will magically change its data, you need to think of a trick to split, retrieve data. I've provided you with these techniques so you need to adopt it properly according to how you want them to be. Also try reading every post/comment its helps.

If you will go back to my post I did ask how you will be inserting the data to database where I ask if the items will all be added on one/same field or different. The sample I made there was for when you want to add it to same field. To add it to different fields where each field will have its own item then you need to have either an array or many variables where each variable will hold one item instead of append it to one variable.

[hhaannyy]

ok thank you so much

[Mr.M]

Cool. If your question/problem has been solved, you can mark it as solved.