VB.NET Class with Function not displaying Stored Procedure results

See more:

VB

stored-procedure

Hello,

I'm doing a project where I'm using a Class(Class is separate form) with a Function that Reads a Stored Procedure that allows me to Filter/Search records that are displayed in my Gridview. I CAN get the code to work correctly when I don't use the Class, but...I need it to work WITH the class. The end result I'm looking for is to be able to Filter/Search the populated GridView. When I do run this code I get my custom alert saying 'No Records Found' But of course there are records.

This is the code in my Class:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class EmpRecords

    Public Shared Function SearchEmpRecords_Sp(searchBy As String, searchVal As String) As DataTable

        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnection").ConnectionString)
        Dim adp As New SqlDataAdapter()
        Dim cmd As New SqlCommand()
        Dim dt As New DataTable()

        Try
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SearchEmpRecords_Sp"
            cmd.Parameters.AddWithValue("@SearchBy", searchBy)
            cmd.Parameters.AddWithValue("@SearchVal", searchVal)
            adp.SelectCommand = cmd
            adp.Fill(dt)
        Finally
            dt.Clear()
            dt.Dispose()
            adp.Dispose()
            con.Close()
        End Try
        Return dt
    End Function


End Class

This is my code in the aspx page

Private Sub getEmpRecords(searchBy As String, searchVal As String)

        Try
            Dim dt = EmpRecords.SearchEmpRecords_Sp(searchBy, searchVal)
            If dt.Rows.Count > 0 Then
                grdEmp.DataSource = dt
                grdEmp.DataBind()
            Else
                grdEmp.DataSource = Nothing
                grdEmp.DataBind()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

There is other code that helps the Filter/Search but like I said I can get the code to work without using the class, But I really need for it to work with the Class. I don't know if you need more info from me in order to help me with this. Also when I do run this code I get my custom alert saying 'No Records Found' But of course there are records. I almost positive I'm connecting to the database but I don't think I'm getting to the Stored Procedure. My Stored Procedure works fine and my GridView does get populated.

Any help will be greatly appreciated. Thanks