[christian Mukeba]

i am having a problem saving multiple listbox items into a database table
here's my scenario, i have 2 listboxes and 2 buttons (add and remove) and its works as follow:
my first listbox (listbox1) contains all my items and my second listbox (accessorieslistbox) get items from the first listbox when the add button is cliked (the user select as many item as he/she want then add them in the second listbox)and the remove button does the inverse
and then after selecting all the needed items the user click on the bindingnsvigatorsaveitem to save all the item into my table
Here's what i have:

 Private Sub Unit_OrderBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Unit_OrderBindingNavigatorSaveItem.Click
        Dim conn As New SqlClient.SqlConnection(My.Settings.NewEvapcoDBConnectionString.ToString)
        Dim j As Integer = Me.AccessoriesListBox.Items.Count

        Try
            conn.Open()

            BindingNavigatorAddNewItem.Visible = True
            If UnitSerialNumberTextBox.Text = "" Then
                ' Throw New Global.System.ArgumentNullException("UnitSerialNumber")
                MessageBox.Show("Enter the Unit Serial Number ", "Unit Order")
            Else
                Me.Validate()
                Me.Unit_OrderBindingSource.EndEdit()
                Me.Unit_OrderTableAdapter.Update(Me.UnitOrderDataSet.Unit_Order)

                For i As Integer = 0 To j - 1
                    Dim sqlcmd As String = ("INSERT INTO Unit Order(Accessories) values (" & Me.AccessoriesListBox.Items(i) & " ',' ")
                    Dim cmd As New SqlClient.SqlCommand(sqlcmd, conn)
                    cmd.ExecuteNonQuery()
                Next

                MessageBox.Show("Update Successful", "Unit Order")

                Me.BindingNavigatorAddNewItem.Visible = True
                Me.BindingNavigatorDeleteItem.Visible = True
            End If
        Catch ex As System.Exception
            MsgBox("An error Occured while saving", vbCritical, "Error")
        End Try

    End Sub

now when i am saving its just saying my exception message :"An Error Occured while saving"
can anyone help me to solve this?please

[raziel_]

hi,
you can start by removing the catch part of your code and see what runtime error you get. Of course your query string is incorrect too:

                   Dim sqlcmd As String = ("INSERT INTO Unit Order(Accessories) values (" & Me.AccessoriesListBox.Items(i) & " ',' ")

As what i seeit ends with ',' which makes it looks somthing like this:INSERT INTO Unit Order(Accessories) values(10','. first your missing the closing bracket and the end in your string (it should be values(10)). Your bracket at the begging is outside the string which is also incorrect you dont need it. Also perhaps i guess you should put "Me.AccessoriesListBox.Items(i)" inside the single quotes. But i would suggest you to use parametarized query. It will protect your program from SQL injections and will make your code easy to read. here is example:

        For i As Integer = 0 To j - 1
            Dim sqlcmd As String = "INSERT INTO Unit Order(Accessories) values (@p1)"

            Dim cmd As New SqlClient.SqlCommand(sqlcmd, conn)
            cmd.Parameters.AddWithValue("@p1", Me.AccessoriesListBox.Items(i))
            cmd.ExecuteNonQuery()
        Next

good luck

This post has been edited by raziel_: 10 October 2012 - 02:52 AM

[artemix22]

double topic : First Topic

[christian Mukeba]

Thanks for your reply raziel,i just tried what you suggested but am still not able to save my listbox items, its doing the same as if nothing as changed

please i need help people

[raziel_]

remove the try - catch block and run your code. see what is the exact error and post the new code you have try with the error.

This post has been edited by raziel_: 10 October 2012 - 05:08 AM

[christian Mukeba]

Here's my new code:

 Private Sub Unit_OrderBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Unit_OrderBindingNavigatorSaveItem.Click
        Dim conn As New SqlClient.SqlConnection(My.Settings.NewEvapcoDBConnectionString.ToString)
        Dim j As Integer = Me.AccessoriesListBox.Items.Count

        'Try
        conn.Open()

        BindingNavigatorAddNewItem.Visible = True
        If UnitSerialNumberTextBox.Text = "" Then
            ' Throw New Global.System.ArgumentNullException("UnitSerialNumber")
            MessageBox.Show("Enter the Unit Serial Number ", "Unit Order")
        Else
            Me.Validate()
            Me.Unit_OrderBindingSource.EndEdit()
            Me.Unit_OrderTableAdapter.Update(Me.UnitOrderDataSet.Unit_Order)

            For i As Integer = 0 To j - 1
                Dim sqlcmd As String = "INSERT INTO Unit Order(Accessories) values (@Basin Level Platform, @Fan Vibration Switch, @High Temp Eliminator)"
                Dim cmd As New SqlClient.SqlCommand(sqlcmd, conn)
                cmd.Parameters.AddWithValue("@Basin Level Platform, @Fan Vibration Switch, @High Temp Eliminator", Me.AccessoriesListBox.Items(i))

                'Dim sqlcmd As String = ("INSERT INTO Unit Order(Accessories) values (" & Me.AccessoriesListBox.Items(i) & " ',' ")

                cmd.ExecuteNonQuery()
            Next

            MessageBox.Show("Update Successful", "Unit Order")

            Me.BindingNavigatorAddNewItem.Visible = True
            Me.BindingNavigatorDeleteItem.Visible = True
        End If
        'Catch ex As System.Exception
        MsgBox("An error Occured while saving", vbCritical, "Error")
        'End Try

    End Sub

and i am not receiving any error message its just frizing

This post has been edited by christian Mukeba: 10 October 2012 - 05:38 AM

[PeterH]

You are specifying 3 @ parameters but only supplying 1 data value, there should be a 1 to 1 match.

 
cmd.Parameters.AddWithValue("@Basin Level Platform, @Fan Vibration Switch, @High Temp Eliminator", Me.AccessoriesListBox.Items(i)) 

[christian Mukeba]

maybe i am stupid but i am still learning.....as i explain in my first post i have 2 listboxes, in the first listbox i have all the items that will be pass to the second listbox but now i am asking does all those items in my first listbox need to be include in my table in order to save them or there is no need to do that!! sorry if my question is stupid..and please how can i post a pic of my GUI so that its can be easy to help me

[raziel_]

Is there any chance that you're using ListView and not ListBox?
Also for each parameter in your query(the @p1 things) you must add a value:

cmd.Parameters.AddWithValue("@Basin Level Platform", "SomeValue") 
cmd.Parameters.AddWithValue("@Fan Vibration Switch", "SomeOtherValue") 

etc

[PeterH]

You dont say in your original post where the items contined in your listbox1 are coming from. If you have already retrieved these from your database then there is no reason to save them back again. It depends on what you are trying to achieve here.

[christian Mukeba]

@raziel
i am using listbox not listview and the value that you said i must add where do i get them?

@PeterH
the item in my listbox are my own items they dont comes from no where their are my own items that i put in the listbox using (Edit Items...) and once the user select the items that he/she want to use and add them in the second listbox then click on save i want all those items to be save separate by a comma

[PeterH]

Ok then, assuming the database table exists and is ready to receive your data from listbox2, you need to add only 1 @parameter and the listbox2.item.

 Dim sqlcmd As String = "INSERT INTO Unit Order(Accessories) values (@Parameter"

then for each item in your listbox2

cmd.Parameters.AddWithValue("@Parameter", ListBox2.Items(index))
cmd.ExecuteNonQuery() 

If, as you also seem to suggest, you want these items 'comma' seperated you will need to concatenate the listbox2 items into a string data item first e.g. mydata += listbox2.item(index) & "," etc. etc. and then add that data item to the AddWithValue in place of the listbox2 reference.

[christian Mukeba]

my database table that i have is unit Order and its contains a column called Accessories and its in that column that i want to put all the items that the user selected and i'v tried this:

 For i As Integer = 0 To j - 1
                    Dim sqlcmd As String = "INSERT INTO Unit Order(Accessories) values (@Basin Level Platform)"
                    Dim cmd As New SqlClient.SqlCommand(sqlcmd, conn)
                    cmd.Parameters.AddWithValue("@Basin Level Platform", Me.AccessoriesListBox.Items(i))

                    'Dim sqlcmd As String = ("INSERT INTO Unit Order(Accessories) values (" & Me.AccessoriesListBox.Items(i) & " ',' ")

                    cmd.ExecuteNonQuery()

its stil not working am really stressed now ( if there is a way i can put an image of my GUI please show me so that you can realy see what i mean)

[raziel_]

to attach image use the full editor option below the edit text field. when the new page opens there is this button again below the text field called Click To Attach Files. when you attach a file you will see up before the button and there is this link called add to post. Also you may want to check Insert Statement and SqlParameter.AddWithValue

[christian Mukeba]

Thanks Below is the image of my 2 listboxes the 1st is where all my items are (those items has been put in the listbox using Edit Items....They don't comes from inside my DB) and the user select items one by one and send them in the 2nd listbox then click on the bindingnavigatorsaveitem to save them in my DB[attachment=31245
:Image.png]
hope this image can help you to help me....

Attached image(s)

•