ljhodgett
asked on
send combobox data to ms sql server 2005 via a stored procedure including a loop in vb.net
Hi,
I have a table called Where_Used that has a column called Rig_ID. The windows form has a combobox called cmbrigs. I know the process of writing a stored procedure but not the code that goes inside it. What I am trying to find out is what code I need to place into the stored procedure and how to write to the procedure in vb.net 2005 to do the following: -
The combobox has multiple separate items in it and I would like the stored procedure to write each individual item to the column rig_ID in the Where_Used table using a loop in the stored procedure instead of using a loop and multiple insert commands in vb.net
Hope this makes sense. In a nut shell I would like the stored procedure to do the loop to enter the combobox data to the table.
Many Thanks
Lee
I have a table called Where_Used that has a column called Rig_ID. The windows form has a combobox called cmbrigs. I know the process of writing a stored procedure but not the code that goes inside it. What I am trying to find out is what code I need to place into the stored procedure and how to write to the procedure in vb.net 2005 to do the following: -
The combobox has multiple separate items in it and I would like the stored procedure to write each individual item to the column rig_ID in the Where_Used table using a loop in the stored procedure instead of using a loop and multiple insert commands in vb.net
Hope this makes sense. In a nut shell I would like the stored procedure to do the loop to enter the combobox data to the table.
Many Thanks
Lee
well, simply put you want to pass a number of undetermined parameters to the stored procedure or in the perfect world a table. Well you cannot do it.
the easies way is to generate a batch sql by looping through your combo box
for i=0 to combobox.items.count-1
sqlStatement = sqlStatement + "EXEC InsertThis @comboItem= ''" + combobox.items[i].ToString
next
sqlConnection.Execute sqlStatement
your procedure would look like
CREATE PROCEDURE InsertThis
@comboItem varchar(125)
AS
INSERT INTO Where_Used (rig_ID) VALUES (@comboItem)
solution 2.
or you could pass all the list of all rigs as comma delimited string
the easies way is to generate a batch sql by looping through your combo box
for i=0 to combobox.items.count-1
sqlStatement = sqlStatement + "EXEC InsertThis @comboItem= ''" + combobox.items[i].ToString
next
sqlConnection.Execute sqlStatement
your procedure would look like
CREATE PROCEDURE InsertThis
@comboItem varchar(125)
AS
INSERT INTO Where_Used (rig_ID) VALUES (@comboItem)
solution 2.
or you could pass all the list of all rigs as comma delimited string
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hiya,
We'll I was hopeing that I would be able to send an array to the stored procedure once and then use the stored procedure to do the insert command.
Sometime there can be 100 + items in the combo box and it sounds as if I will have to run the stored procedure individually for each.
Salutations Fred. It's time to populate "Porker" with my vb.net code hehehhe. Dont worry it's another application not sc!!!!!
Lee
We'll I was hopeing that I would be able to send an array to the stored procedure once and then use the stored procedure to do the insert command.
Sometime there can be 100 + items in the combo box and it sounds as if I will have to run the stored procedure individually for each.
Salutations Fred. It's time to populate "Porker" with my vb.net code hehehhe. Dont worry it's another application not sc!!!!!
Lee
Well, if you change from using an Array to populate your combo box, to a DataTable, then you can use
a DataAdapter and let it manage the SQL calls. In Addition the DataAdapter can be configured to do
a Batch Update, which will send all of the records to SQL at once.
a DataAdapter and let it manage the SQL calls. In Addition the DataAdapter can be configured to do
a Batch Update, which will send all of the records to SQL at once.
dataadapter won't do it through a stored procedure
FDzjuba: Actually a DataAdapter will work through a stored procedure, I do it all the time. I almost
never make a call to the database without using a DataAdapter and stored procedure.
Did you test out the code I posted? It will work.
never make a call to the database without using a DataAdapter and stored procedure.
Did you test out the code I posted? It will work.
Any progress ?
1) Loop your combobox and then call a stored procedure instead of a insert-statement ?
Dim cmd As New SqlCommand
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedur
cmd.CommandText = "YourStoredProcedure"
cmd.Parameters.Add("@param
For i As Integer = 0 To combobox.Items.Count - 1
Dim o As Object = combobox.Items(i)
Dim displayText As String = o.ToString()
cmd.Parameters("@param1").
cmd.executenonquery
Next
2) Give all the combobox-items at once to the stored procedure ?