Multiple user accessing Webservice simultaneously in ASP.Net

See more:

webservice

ASP.NET2

We have a webmethod created in webservice asp.net 2.0 (VB), which is accessed throughout the application very frequently. The webmethod gets the query as parameter and returns the dataset. It works fine when number of users are 1 or 2. If some 30 users are using the system simultaneously webmethod returns the dataset of a different query. We have used synclock(LOCK in C#) option which is of no use.

Public Function ExecuteQry(ByVal StrText As String) As DataSet

        Dim AdoDs As New DataSet()
        Dim SqlDp As New SqlDataAdapter
        Dim SqlCmd As New SqlCommand
        Dim SqlParam As New SqlParameter

        Try
            SyncLock AdoDs
                MakeConnect()
                With SqlCmd
                    .Connection = SqlCon
                    .CommandText = "rExecuteQry"
                    .CommandType = CommandType.StoredProcedure
                End With
                SqlParam = SqlCmd.Parameters.AddWithValue("@strText", Trim(StrText))
                SqlDp.SelectCommand = SqlCmd
                SqlDp.Fill(AdoDs)
                DisposeConnect()
                Return AdoDs
            End SyncLock
        Catch ex As SqlException
            DisposeConnect()
            Debug.Write(ex.Message)
        Finally

            SqlCmd = Nothing
            SqlParam = Nothing
        End Try
    End Function

Below is the Stored Procedure:

ALTER PROCEDURE [dbo].[rExecuteQry] @strText Varchar (max) as Exec( @StrText)