<DataObjectMethodAttribute(DataObjectMethodType.Select)> Public Shared Function GetCoreWellsByPage2(ByVal startIndex As Integer, ByVal maxRows As Integer, ByVal wellname As String, ByVal company As String, ByVal field As String, ByVal api As String, ByVal permit As String, ByVal county As String, ByVal twn As String, ByVal rng As String, ByVal section As String) As List(Of CoreLibrary) coreWellList = New List(Of CoreLibrary) Dim con As New SqlConnection(GetConnectionString) Dim sel As String = "SELECT AGS_ID, WELL_NAME, WELL_NUM, API, PERMIT_NUM, OPERATOR_NAME, SUB_COMNAME, StatusType, WELL_LOCATIONID " & "FROM CoreWH_wells " & "WHERE ((WELL_NAME LIKE @Name) OR (@Name IS NULL)) " & "AND ((OPERATOR_NAME LIKE @Company) OR (@Company IS NULL)) " & "AND ((RES_FIELDNAME LIKE @Field) OR (@Field IS NULL)) " & "AND ((API LIKE @API) OR (@API IS NULL)) " & "AND ((PERMIT_NUM LIKE @Permit) OR (@Permit IS NULL)) " & "AND ((CountyName = @County) OR (@County IS NULL)) " & "AND ((TWN = @Township) OR (@Township IS NULL)) " & "AND ((RNG = @Range) OR (@Range IS NULL)) " & "AND ((SECTION = @Section) OR (@Section IS NULL))" Dim cmd As New SqlCommand(sel, con) If String.IsNullOrEmpty(wellname) Then cmd.Parameters.AddWithValue("@Name", DBNull.Value) Else cmd.Parameters.AddWithValue("@Name", "%" & wellname & "%") End If If String.IsNullOrEmpty(company) Then cmd.Parameters.AddWithValue("@Company", DBNull.Value) Else cmd.Parameters.AddWithValue("@Company", "%" & company & "%") End If If String.IsNullOrEmpty(field) Then cmd.Parameters.AddWithValue("@Field", DBNull.Value) Else cmd.Parameters.AddWithValue("@Field", "%" & field & "%") End If If String.IsNullOrEmpty(api) Then cmd.Parameters.AddWithValue("@API", DBNull.Value) Else cmd.Parameters.AddWithValue("@API", "%" & api & "%") End If If String.IsNullOrEmpty(permit) Then cmd.Parameters.AddWithValue("@Permit", DBNull.Value) Else cmd.Parameters.AddWithValue("@Permit", "%" & permit & "%") End If If String.IsNullOrEmpty(county) Then cmd.Parameters.AddWithValue("@County", DBNull.Value) Else cmd.Parameters.AddWithValue("@County", county) End If If String.IsNullOrEmpty(twn) Then cmd.Parameters.AddWithValue("@Township", DBNull.Value) Else cmd.Parameters.AddWithValue("@Township", twn) End If If String.IsNullOrEmpty(rng) Then cmd.Parameters.AddWithValue("@Range", DBNull.Value) Else cmd.Parameters.AddWithValue("@Range", rng) End If If String.IsNullOrEmpty(section) Then cmd.Parameters.AddWithValue("@Section", DBNull.Value) Else cmd.Parameters.AddWithValue("@Section", section) End If con.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Dim well As CoreLibrary Do While rdr.Read well = New CoreLibrary well.WellName = rdr("WELL_NAME").ToString well.WellNum = rdr("WELL_NUM").ToString well.API = rdr("API").ToString well.Permit = rdr("PERMIT_NUM").ToString well.Company = rdr("OPERATOR_NAME").ToString well.Commodity = rdr("SUB_COMNAME").ToString well.Status = rdr("StatusType").ToString well.WellLocationID = CInt(rdr("WELL_LOCATIONID")) well.AGS_ID = rdr("AGS_ID").ToString coreWellList.Add(well) Loop rdr.Close() Dim rowCount As Integer = coreWellList.Count If startIndex + maxRows > rowCount Then maxRows = rowCount - startIndex End If Dim pageList As New List(Of CoreLibrary) Dim rowIndex As Integer For i As Integer = 0 To maxRows - 1 rowIndex = i + startIndex pageList.Add(coreWellList(rowIndex)) Next Return pageList End Function Public Shared Function SelectCount() As Integer Return coreWellList.Count End Function
Can you confirm the query, as written, returns records?
What if you change the query to...
" SELECT AGS_ID, WELL_NAME, WELL_NUM, API, PERMIT_NUM, OPERATOR_NAME, SUB_COMNAME, StatusType, WELL_LOCATIONID FROM CoreWH_wells "