Submit repeating/multiple items to stored procedure via webservice

[barmatt80]

I am trying to what i think is an easy task, but so far i have came up empty.

What i want to do is take a repeating table on a form i have and submit the employee IDs to a database using a web service.

I know how to submit them one by one, but i am thinking there is an easier way to do this instead of send the value to the web service, submit thee data, and then go till the last record.

I am currently using this:

<WebMethod(Description:="sends employee id to database")> _
    Public Function SendMultiple(ByVal strEmployeeID as string) As String

        Dim sqlCon As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        sqlCon.ConnectionString = "connection string information"

        If sqlCon.State = ConnectionState.Closed Then
            sqlCon.Open()
        End If

        sqlCmd.CommandText = "spInsertMultiple"
        sqlCmd.Connection = sqlCon
        sqlCmd.CommandType = CommandType.StoredProcedure

        Dim Parameter0 As SqlParameter = New SqlParameter("@EmployeeID", strEmployeeID)

        Parameter0.Direction = ParameterDirection.Input

        sqlCmd.Parameters.Add(Parameter0)

        Dim dr As SqlDataReader
        dr = sqlCmd.ExecuteScalar

        Dim strReturnedValue As String = "hello"

        Return strReturnedValue

    End Function

Any ideas??? I am stumped. thanks again for your help

[ThatThatGuy]

The code is perfect.... nothing seems to be wrong with it

[barmatt80]

The problem i am having is that code works great for inserting one record. But say I had 10 or 20 ID's i needed to submit. is there a way that I can do a for each loop or something to submit them via one call or do i have to submit them one by one?

[ThatThatGuy]

if you re using this webmethod to get data from aspxpage using ajax.. then
you can send them as a string array of ids....
And read them through javascript.... As like any javascript object array

[Frinavale]

Pass an array of IDs to the method and the loop through the arrays to add them to your database.

Or leave your method the way it is and in your calling code loop through the array of IDs and call the web method to insert each individual one.

Either way works, both ways use an array of IDs so that you can easily loop.

-Frinny

[barmatt80]

Thanks guys. This time we looped through via the code. But i am going to work next week to get a webservice to accept an array and then look through.

where should i put the loop in the web service? just at the execute part, or loop through all the code?

[Frinavale]

I've already answered this question.

You should loop in your Client code (not the web service) since that is where the IDs are available to you (you cannot access these in the web service unless you pass the array to it).

-Frinny