send combobox data to ms sql server 2005 via a stored procedure including a loop in

Posted on 2007-08-03
Last Modified: 2013-11-26

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

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
Question by:ljhodgett
    LVL 53

    Expert Comment

    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"

    2) Give all the combobox-items at once to the stored procedure ?
    LVL 7

    Expert Comment

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

    sqlConnection.Execute sqlStatement

    your procedure would look like

    @comboItem varchar(125)
    INSERT INTO Where_Used (rig_ID) VALUES (@comboItem)

    solution 2.

    or you could pass all the list of all rigs as comma delimited string
    LVL 27

    Accepted Solution

    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
        INSERT INTO Where_Used
                --List all of the columns from your table here.
                (Rig_ID, Location)
                --List all of the parameters here
                --      (1 for each column in your table)
                (@Rig_ID, @Location);

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

            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


        End Sub

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


    Author Comment


    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 code hehehhe. Dont worry it's another application not sc!!!!!

    LVL 27

    Expert Comment

    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.

    LVL 7

    Expert Comment

    dataadapter won't do it through a stored procedure
    LVL 27

    Expert Comment

    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.

    LVL 53

    Expert Comment

    Any progress ?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video discusses moving either the default database or any database to a new volume.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now