[christian Mukeba]

Hi!!!!!!!!!!!!!

heres my problem i have a 2 different listbox and 2 buttons (ADD and REMOVE) now my first listbox contains all my items and the scenario work like this: *the user must select the item in the first listbox (as many as he want) then click on tha ADD button then send that particular item to the second listbox now what i want is all the items that are in the second listbox must be saved into my database at the same time how can i achieve that??? anyone with a clue can help me

Thanks in advance

Here is my code:

Dim conn As New SqlClient.SqlConnection(My.Settings.NewEvapcoDBConnectionString.ToString)
Dim cmd As New SqlClient.SqlCommand("Insert Into Unit Order values (accessorieslistbox)")
Dim a As Integer
Dim item As String
Dim i As Integer

cmd.Parameters.Add("accessorieslistbox", SqlDbType.NVarChar, 50)

For i = 0 To 0
i < accessorieslistbox.SelectedItems.Count
a = i + 1
Next

item = AccessoriesListBox.SelectedItems(i).ToString
cmd.Parameters("accessorieslistbox").Value = item
cmd.ExecuteReader()

cmd.Dispose()
conn.Close()

[Vishal1419]

http://www.dreaminco...abase-in-vbnet/

This coding might be somewhat helpful to you

[christian Mukeba]

hmmmmm!!!!!!i have changed my code to this:

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 i As Integer = Me.AccessoriesListBox.Items.Count

Try
conn.Open()

BindingNavigatorAddNewItem.Visible = True
If UnitSerialNumberTextBox.Text = "" Then
Throw New Global.System.ArgumentNullException("UnitSerialNumber")
Else
Me.Validate()
Me.Unit_OrderBindingSource.EndEdit()

For a As Integer = 0 To i - 1
Dim cmd As New SqlClient.SqlCommand("Insert Into Unit Order (accessories) values (" & AccessoriesListBox.Items(a) & ")")
cmd.ExecuteNonQuery()
Next


Me.Unit_OrderTableAdapter.Update(Me.UnitOrderDataSet.Unit_Order)

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

Me.BindingNavigatorAddNewItem.Visible = True
Me.BindingNavigatorDeleteItem.Visible = True
End If
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
conn.Dispose()
conn.Close()
End Sub

but now its saying EXECUTENONQUERY:CONNECTIONS HAS NOR BEEN INITIALIZED

Can anyone help!!!

[trevster344]

Please use the code tags so it's easier for us to read.

Dim cmd As New SqlClient.SqlCommand("Insert Into Unit Order (accessories) values (" & AccessoriesListBox.Items(a) & ")")

Your error is here. You've only given the command text parameter to the initialization of your object, but you also need to tell it what SQL connection it is to be used for. So after your first parameter, place your sql connection object as a second parameter.

Dim cmd As New SqlClient.SqlCommand("Insert Into Unit Order (accessories) values (" & AccessoriesListBox.Items(a) & ")", conn)

This is how it should work.

[christian Mukeba]

Hi Trevster!!

Sorry for being quiet....i have tried to use your suggestion but am still not able to save my selected items into my table...
Is ther anythng i am doing wrong?
Here's how my code look right now

 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)

        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 Me.AccessoriesListBox.Items.Count - 1
                    Dim sqlcmd As String = ("INSERT INTO Unit Order(accessories) values (" & 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

[trevster344]

Is Unit Order the actual name of the table?

[christian Mukeba]

yes Unit Order is the name of the table

[trevster344]

Well at this point you need to figure out what the error is. I would suggest modifying your try catch block at the error handler. Specifically your messagebox. Make it display the error

Messagebox.show(ex.message)

That will be your best hint at what the issue is.

[christian Mukeba]

Those are my 2 listboxes and the items that are in the first i put them in using the Edit Items...they don't come from the database and now i am thinking of creating a table with all those items in but am not sure if that the solution to my problem...What do you think?
and when i remove my exception msg i am getting an error saying
"Incorrect Syntax near ''" and "must declare the scalar variable '@basin'"

This post has been edited by christian Mukeba: 11 October 2012 - 01:08 AM

[CharlieMay]

Unit Order is not a good way to name a table, the space is throwing off the SQL parser. Either remove the space from your database or use the [] wrapper to denote that it is indeed one item
INSERT INTO [Unit Order] ...

This post has been edited by CharlieMay: 11 October 2012 - 05:02 AM

[christian Mukeba]

Thanks charlieMay i have alrdy fix that problem now my only problem is that i want to insert many items in the Accessories column how do i do that?

[CharlieMay]

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

You need to wrap the item in ' for text fields

Dim sqlcmd as String = ("INSERT INTO [Unit Order] (Accessories) Values ('" & AccessoriesListBox.Items(i) & "')")

But an even better way is to use a parameter so you don't run into these issues

For i As Integer = 0 To Me.AccessoriesListBox.Items.Count - 1
  Dim sqlcmd as String = ("INSERT INTO [Unit Order] (Accessories) Value (@Accessory)")
  Dim cmd as new SqlClient.SqlCommand(sqlcmd, conn)
  cmd.Parameters.AddWithValue("@Accessory", AccessoriesListBox.Items(i))
Next

[christian Mukeba]

for now i have something like this:

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

but i want to place more parameters (for instance @BAsinLevelPlatform,@banana,@mango,@pen,@book etc...)in the accessories column is that possible? bcoz when am trying to do that am getting an error sayng the nomber of parameters must match the number of columns..

[CharlieMay]

I think you're confused on a couple of things.

First You still have Unit Order instead of [Unit Order] or whatever the table is actually named.

Your parameter is @Basin but you give it @BasinLevelPlatform in your parameter definition.

This code loops through the list and inserts a NEW row each time in the database if you are only inserting one field you only need one Value so if you are inserting

• Basin Level Platform
  
• Electronic Water Level Control Package
  
• etc...

Each loop will take the next item in the list and add it as a new row in your table

[christian Mukeba]

sorry that ws my old code..,the new one look like this:

For i As Integer = 0 To Me.AccessoriesListBox.Items.Count - 1    
                   Dim sqlcmd As String = "INSERT INTO [Unit Order] (Accessories) values (@BasinLevelPlatform)"  
                   Dim cmd As New SqlClient.SqlCommand(sqlcmd, conn)    
                     cmd.Parameters.AddWithValue("@BasinLevelPlatform", Me.AccessoriesListBox.Items(i))    
                   cmd.ExecuteNonQuery()    
               Next 

but now am asking if its posible to have something like:

For i As Integer = 0 To Me.AccessoriesListBox.Items.Count - 1    
                   Dim sqlcmd As String = "INSERT INTO [Unit Order] (Accessories) values (@BasinLevelPlatform,@ElectronicWaterLevelControlPackage,etc...)"  
                   Dim cmd As New SqlClient.SqlCommand(sqlcmd, conn)    
                     cmd.Parameters.AddWithValue("@BasinLevelPlatform,@ElectronicWaterLevelControlPackage"etc.., Me.AccessoriesListBox.Items(i))    
                   cmd.ExecuteNonQuery()    
               Next 

when i try that its not working, how can i achieve that in a right way please>>>