Click here to Skip to main content
15,847,264 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I Mange to solve my problem earlier inserting a listbox items into database, but only the 1st item in the listbox is being inserted.

What I have tried:

This is my code:

VB
Try
          connect()
          connection.Open()
          Dim queryString As String
          queryString = "Insert into ServiceRecords([Personnel]) Values(@Personnels)"
          command = New OleDbCommand(queryString, connection)
          For i As Integer = 0 To Me.ListBox1.Items.Count + 1
              command.Parameters.AddWithValue("@Personnels", ListBox1.Items(i))
          Next
      Catch ex As Exception

      End Try
Posted
Updated 5-Feb-18 23:16pm
Comments
Maciej Los 6-Feb-18 3:01am    
I doubt it, becase your command is never executed...
All what you need to do is to create command and execute it (using ExecuteNonQuery() method) inside a for-next loop.
Got it?
WinterPrison 6-Feb-18 3:18am    
Tnx, but it didnt fix my problem.
Maciej Los 6-Feb-18 3:20am    
Are you sure?

1 solution

Your loop is all wrong; you are adding every listbox item to the same parameter name. And you never execute any command so nothing will go to the database. Your loop needs to take each ListBox item in turn, add it to the parameter set as you are doing, and then use ExecutNonQuery (as Maciej already told you), to send the item to the database.
VB
queryString = "Insert into ServiceRecords([Personnel]) Values(@Personnels)"
command = New OleDbCommand(queryString, connection)
For i As Integer = 0 To Me.ListBox1.Items.Count + 1
    command.Parameters.AddWithValue("@Personnels", ListBox1.Items(i))
    command.ExecuteNonQuery()
    command.Parameters.Clear()
Next

See OleDbCommand.ExecuteNonQuery Method (System.Data.OleDb)[^].
 
Share this answer
 
v2
Comments
WinterPrison 6-Feb-18 4:39am    
Sorry to ask this Richard i now get that ExecuteNonQuery() part, but how should i do the looping part

For i As Integer = 0 To Me.ListBox1.Items.Count + 1
    command.Parameters.AddWithValue("@Personnels", ListBox1.Items(i))
    command.ExecuteNonQuery()
Next
Richard MacCutchan 6-Feb-18 4:53am    
See my updated code. You should study the documentation for all these methods as I may well be doing something wrong.
Maciej Los 6-Feb-18 4:48am    
Richard, small note: a command should be created inside a for-next loop too due to increase of count of parameters in each step of loop.
Richard MacCutchan 6-Feb-18 4:50am    
Thanks, I missed that bit. You can guess I am not an SQL programmer.
Maciej Los 6-Feb-18 4:53am    
You're very welcome :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900