Listbox: Insert Multiple Items into DB

[zebedeu]

I have 2 listboxes, one pulling a list of Jobs from a DB, the other with Employees from the DB as well.

I want the user to be able to choose only 1 Job and 1 or many Employees, and insert the selections into a table called Times, using a Formview and SQL datasource. I've read a lot about this, but the truth of the matter is that I never got it to work.

Thanks for any help.

 

[Anti-Rich]

hi,​

im a little unclear about the whole "formview", im not sure what that means
(it probably really simple, haha, but it doesnt ring any bells) but you could
try this. im assuming your fields in your table are JID, and EID and that you
have a connection set up somewhere else in your code​

[LEFT]Dim sql as string
Dim sqlarray() as string
Dim cmd as sqlcommand[/LEFT]
 
 
 
 
 

For I as integer=0 to firstlistbox.items.count-1[INDENT][LEFT]If firstlistbox.items(i).selected then[/LEFT]
[INDENT][LEFT]For x as integer = o to secondlistbox.items.count-1[/LEFT]
[/INDENT]

[INDENT]If secondlistbox.items(x).selected then [/INDENT][INDENT][INDENT][LEFT]Sql = "INSERT INTO Times (JID, EID) VALUES (‘" & jobarray(i) & "’, ‘" & 
employeearra(x) & "’)[/LEFT]
[/INDENT]
 

[INDENT]Redim preserve sqlarray(sqlarray.length)[/INDENT][INDENT]Sqlarray(sqlarray.length-1) = sql[/INDENT][INDENT]end if[/INDENT][LEFT]next[/LEFT]

 
 
 
 
 
 

[/INDENT]End if 
 
 
 
 

[/INDENT]Next 
 
 
 
 
[LEFT]Cmd = new sqlcommand[/LEFT]
 
 
 
 

With cmd[INDENT][LEFT].connection = cnn[/LEFT]
[/INDENT]

[INDENT].commandtype=commandtype.text[/INDENT][INDENT].connection.open[/INDENT][INDENT]For I as integer=0 to sqlarray.length-1[/INDENT][INDENT][INDENT][LEFT].commandtext=sqlarray(i)[/LEFT]
[/INDENT]

[INDENT].executenonquery()[/INDENT][LEFT]Next[/LEFT]

 
 
 
[LEFT].connection.close[/LEFT]
 
 
 
 

[/INDENT]End with

hope that helps! let me know if it works, or you run into problems and ill help further. if any one else can suggest improvements to my code, go for it. im always looking to create better and more effeicient code.​

regards
adam​

 

[zebedeu]

Hi, thanks for your help. A formview is a data display/management control with the .net 2.0 framework. I have discovered that formviews have methods, and one in specific that triggers just before an insert occurs, "ItemInserting". So i'm coding under this method the following code:

Sub empAssignFormview_ItemInserting(ByVal sender As Object, ByVal e As FormViewInsertEventArgs)

Dim i As Integer
insertEmployeeStatus2.Text = "You Selected" & " "

For i = 0 To lbEmp.Items.Count - 1
If lbEmp.Items(i).Selected Then
insertEmployeeStatus2.Text = insertEmployeeStatus2.Text & lbEmp.Items(i).Text & " "

End If
Next

End Sub

And this works fine. As you can tell its just shooting the selected values into the label "insertEmployeeStatus2".

Now can anyone tell me how to insert those selected values into the sqlDatasource so the values will actually go into the Database?


Thank you!

 

[Anti-Rich]

hey

yeah no problem, glad i could be of some assistance. couldn't you just modify the code to adapt to work with a formview? i have never used a formview sorry otherwise i might have been able to help you more... i think i need to make the move to 2005! hahah

good luck, any questions id be glad to help

regards
adam