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
ljhodgettAsked:
Who is Participating?
 
VBRocksCommented:
Here is an example of how you actually create a stored procedure.

Go into your SQL database, and open a New Query window, then insert the following.  Note, you will
have to modify the parameters and column names to match what is in your table.  After you have
modified it, when you are ready, Execute the query, and it will create a stored procedure for you,
named "InsertWhere_Used".  Please note that I added an extra column named "Location" just as an
example, to show you how to handle multiple parameters and columns.

CREATE PROCEDURE InsertWhere_Used
(
    -- Create parameters for all of the columns in your table here
    --    and separate them with a comma
    @Rig_ID            int,
    @Location      Varchar(50)  --This is an example column
)
AS
BEGIN
      
    INSERT INTO Where_Used
            --List all of the columns from your table here.
            (Rig_ID, Location)
    VALUES
            --List all of the parameters here
            --      (1 for each column in your table)
            (@Rig_ID, @Location);
END
GO


From with SQL, to use the Stored Procued, perform the following:
    Open a New Query winow and insert the following code, then click Execute:
        exec InsertWhere_Used 2, "Texas";




From within Visual Basic, here is an example of loading a combobox, and updating SQL using the
combobox and the stored procedure we created above.

'First, call this sub when the form opens (You will have to modify the SELECT statement below
'    to return the Rig_ID, and whatever other column you want displayed in your combobox,
'    in addition to specifying the table it is pulling it's data from.

'    In this example, I setup an SQL table called "Rigs" and I am pulling the Rig_ID and
'    the Rig_Name to load into the combobox

'    * You will also have to modify the connection string in both subs below.

    Private Sub LoadComboBox()
        '*You need to change the connection string
        Dim connectionString As String = _
            "Data Source=DataPrint2;Database=Test;Integrated Security=SSPI;"


        Dim cn As New SqlClient.SqlConnection(connectionString)
        Dim da As New SqlClient.SqlDataAdapter()
        da.SelectCommand = New SqlClient.SqlCommand()
        With da.SelectCommand
            .Connection = cn
            .CommandType = CommandType.Text

            .CommandText = _
                "SELECT Rig_ID, Rig_Name FROM Rigs ORDER BY Rig_Name ASC"
        End With

        Dim dt As New DataTable("Rigs")
        da.Fill(dt)

        Me.cmbrigs.DisplayMember = "Rig_Name"
        Me.cmbrigs.ValueMember = "Rig_ID"
        Me.cmbrigs.DataSource = dt
    End Sub



'Next, when you are ready to update your SQL database, call this procedure - you can call it from
'a button, or whatever:

    Private Sub Update_Where_Used_Table()

        'Get a reference to your table
        Dim dt As DataTable = DirectCast(Me.cmbrigs.DataSource, DataTable)

        '*You need to change the connection string
        Dim connectionString As String = _
            "Data Source=DataPrint2;Database=Test;Integrated Security=SSPI;"


        Dim cn As New SqlClient.SqlConnection(connectionString)
        Dim da As New SqlClient.SqlDataAdapter()
        da.InsertCommand = New SqlClient.SqlCommand()
        With da.InsertCommand
            .Connection = cn

            'Specify Stored Procedure as the CommandType
            .CommandType = CommandType.StoredProcedure

            'Since we are using a Stored Procedure, enter the name of it here.
            .CommandText = "InsertWhere_Used"

            'Since we are using a Stored Procedure, then we have to add
            '    parameters, set the data type, and tell it what column
            '    the data is coming from.
            .Parameters.Add("@Rig_ID)", SqlDbType.Int, 0, "Rig_ID")

        End With

        'This will actually insert all of the items in your combobox
        '    to the table
        da.Update(dt)

        MsgBox("Done")

    End Sub


Hopefully that will help you, and it's what you are looking for.  Let me know if you have any
questions.


0
 
DhaestCommented:
What exactly do you want ?
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.StoredProcedure
cmd.CommandText = "YourStoredProcedure"
cmd.Parameters.Add("@param1", SqlDbType.Text)
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").Value = "20060101"
      cmd.executenonquery
Next

2) Give all the combobox-items at once to the stored procedure ?
0
 
FDzjubaCommented:
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
 
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
ljhodgettAuthor Commented:
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
0
 
VBRocksCommented:
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.



0
 
FDzjubaCommented:
dataadapter won't do it through a stored procedure
0
 
VBRocksCommented:
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.

0
 
DhaestCommented:
Any progress ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.