I checked your Code and It look fine . Also i used same code for using SqlConnection It work in both cases With parameter and without parameter. Just i called to set your DBCon.Connectionstring as it was not set in the method and removed DBCon.Open() from code as it not required when using DataAdapter. I used Northwind Database for the sample.
VB.Net
Partial Class _111908
Inherits System.Web.UI.Page
Private DBCon As New SqlConnection
Private DBCmd As SqlCommand
' DB DATA
Public DBDA As SqlDataAdapter
Public DBDT As DataTable
' QUERY PARAMETERS
Friend Params As New List(Of SqlParameter)
' QUERY STATISTICS
Public RecordCount As Integer
Public Exception As String
Public Sub New()
End Sub
Public Sub New(ConnectionString As String)
DBCon.ConnectionString = ConfigurationManager.ConnectionStrings("ConStr1").ConnectionString
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
'Without parameterize query
ExecQuery("SELECT * FROM Orders")
'With parameterize query
Params.Add(New SqlParameter("@CustomerID", "VINET"))
Params.Add(New SqlParameter("@EmployeeID", "5"))
ExecQuery("SELECT * FROM Orders Where CustomerID = @CustomerID AND EmployeeID = @EmployeeID")
End If
End Sub
' EXECUTE QUERY SUB
Public Sub ExecQuery(Query As String)
RecordCount = 0
Exception = ""
Try
DBCon.ConnectionString = ConfigurationManager.ConnectionStrings("ConStr1").ConnectionString
DBCon.Open()
' CREATE DB COMMAND
DBCmd = New SqlCommand(Query, DBCon)
' LOAD PARAMS INTO DB COMMAND
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
' CLEAR PARAM LIST
Params.Clear()
' EXECUTE COMMAND & FILL DATASET
DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBDT)
Catch ex As Exception
End Try
End Sub
End Class
Also check the below article link for your reference How to use DataAdapter in MYSQL.