?
Solved

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

Posted on 2007-08-03
8
Medium Priority
?
592 Views
Last Modified: 2013-11-26
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
0
Comment
Question by:ljhodgett
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 19624229
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
 
LVL 7

Expert Comment

by:FDzjuba
ID: 19625601
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
 
LVL 27

Accepted Solution

by:
VBRocks earned 2000 total points
ID: 19625734
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:ljhodgett
ID: 19625767
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
 
LVL 27

Expert Comment

by:VBRocks
ID: 19625803
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
 
LVL 7

Expert Comment

by:FDzjuba
ID: 19637213
dataadapter won't do it through a stored procedure
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19638586
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 19789426
Any progress ?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Screencast - Getting to Know the Pipeline
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

809 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