insert multiple listbox items into the database in vb

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 5949 Views - Last Post: 15 October 2012 - 05:57 AM Rate Topic: -----

#1 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

insert multiple listbox items into the database in vb

Posted 10 September 2012 - 12:39 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: insert multiple listbox items into the database in vb

#2 Vishal1419  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 141
  • Joined: 19-May 12

Re: insert multiple listbox items into the database in vb

Posted 10 September 2012 - 01:15 AM

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

This coding might be somewhat helpful to you
Was This Post Helpful? 0
  • +
  • -

#3 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: insert multiple listbox items into the database in vb

Posted 10 September 2012 - 02:22 AM

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!!!
Was This Post Helpful? 0
  • +
  • -

#4 trevster344  Icon User is offline

  • The Peasant
  • member icon

Reputation: 224
  • View blog
  • Posts: 1,499
  • Joined: 16-March 11

Re: insert multiple listbox items into the database in vb

Posted 10 September 2012 - 08:48 AM

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.
Was This Post Helpful? 0
  • +
  • -

#5 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: insert multiple listbox items into the database in vb

Posted 10 October 2012 - 12:03 AM

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

Was This Post Helpful? 0
  • +
  • -

#6 trevster344  Icon User is offline

  • The Peasant
  • member icon

Reputation: 224
  • View blog
  • Posts: 1,499
  • Joined: 16-March 11

Re: insert multiple listbox items into the database in vb

Posted 10 October 2012 - 08:58 AM

Is Unit Order the actual name of the table?
Was This Post Helpful? 0
  • +
  • -

#7 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: insert multiple listbox items into the database in vb

Posted 10 October 2012 - 10:13 PM

yes Unit Order is the name of the table
Was This Post Helpful? 0
  • +
  • -

#8 trevster344  Icon User is offline

  • The Peasant
  • member icon

Reputation: 224
  • View blog
  • Posts: 1,499
  • Joined: 16-March 11

Re: insert multiple listbox items into the database in vb

Posted 11 October 2012 - 12:49 AM

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.
Was This Post Helpful? 0
  • +
  • -

#9 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: insert multiple listbox items into the database in vb

Posted 11 October 2012 - 01:06 AM

Attached Image
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

Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: insert multiple listbox items into the database in vb

Posted 11 October 2012 - 05:01 AM

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

Was This Post Helpful? 0
  • +
  • -

#11 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: insert multiple listbox items into the database in vb

Posted 11 October 2012 - 05:12 AM

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?
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: insert multiple listbox items into the database in vb

Posted 11 October 2012 - 05:21 AM

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

Was This Post Helpful? 0
  • +
  • -

#13 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: insert multiple listbox items into the database in vb

Posted 11 October 2012 - 05:35 AM

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..
Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: insert multiple listbox items into the database in vb

Posted 11 October 2012 - 06:11 AM

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
Was This Post Helpful? 0
  • +
  • -

#15 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: insert multiple listbox items into the database in vb

Posted 11 October 2012 - 06:36 AM

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>>>
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2