I want to perform a query search with two user inputs from a textbox and display the result on a Viewgrid but I'm having problems with the parameters I want to insert in the query and I get the following error:
ExecQuery error:
Line 1: Incorrect syntax near '.'.
Must declare the variable '@H'.
Did I implement a incorrect function or am I doing this in an incorrect manner or if I'm simply trying to fill the viewgrid incorrectly? I'll appreciate if someone can point me in the right direction.
This is my SQLControl code:
Public Class SQLControl
ReadOnly DBCon As New SqlConnection()
Private DBCmd As SqlCommand
'DB Data
Public DBDA As SqlDataAdapter
Public DBDT As DataTable
'Query Parameters
Public Params As New List(Of SqlParameter)
'Query Statistics
Public RecordCount As Integer
Public Exception As String
'This generates a blank sqlclient class with the deafult connection string
Public Sub New()
End Sub
'Allow connection string override
Public Sub New(connectionString As String)
DBCon = New SqlConnection(connectionString)
End Sub
'Execute Query Sub
Public Sub ExecQuery(query As String)
'Reset Query Stats
RecordCount = 0
Exception = ""
Try
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
'Capture Error
Exception = "ExecQuery Error: " & vbNewLine & ex.Message
Finally
'Close Connection
If DBCon.State = ConnectionState.Open Then DBCon.Close()
End Try
End Sub
'Add Params
Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New SqlParameter(Name, Value)
Params.Add(NewParam)
End Sub
'Error Checking
Public Function HasException(Optional Report As Boolean = False) As Boolean
If String.IsNullOrEmpty(Exception) Then Return False
If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
Return True
End Function
End Class
This is the button where I execute the query:
Protected Sub ExecuteButton_Click(sender As Object, e As EventArgs) Handles ExecuteButton.Click
If StoreIDTextbox.Text.Length <= 0 Then
MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
End If
If TransactionIDTextbox.Text.Length <= 0 Then
MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
End If
SQL.AddParam("@H.Str_ID", Integer.Parse(StoreIDTextbox.Text))
SQL.AddParam("@H.Tran_ID", Integer.Parse(TransactionIDTextbox.Text))
SQL.ExecQuery("SELECT H.Emp_ID, H.Cust_ID, D.*
FROM Transaction_Header H
INNER JOIN LN_Detail L ON (H.Str_ID = L.Str_ID AND H.Rgst_ID = L.Rgst_ID AND H.Tran_ID = L.Tran_ID)
INNER Join LN_Discount D ON (L.Str_ID = D.Str_ID AND L.Rgst_ID = D.Rgst_ID AND L.Tran_ID = D.Tran_ID AND L.Tran_LN_Num = D.Tran_LN_Num)
WHERE H.Str_ID = @H.Str_ID AND H.Tran_ID = @H.Tran_ID")
If SQL.HasException(True) Then Exit Sub
GridView1.DataSource = SQL.DBDT
End Sub
In your class:
I have moved your database objects to the methods where they are used so they can be closed and disposed. Using...End Using blocks do this for you.
If you need a record count, it is available from DataTable.Rows.Count
If you do not plan to handle exceptions within your class, then don't put the Try...End Try there. One of the points of having a data access class is to separate the user interface from the data source. Showing a message box in you data access class hard codes a link to the user interface. Your class shouldn't be tied to a desktop app in Windows that can display a message box. Suppose you want to use you class in a web app. You would have to rewrite the class. I put the Try...End Try in the button event (user interface code) where you can display a message box.
I left the parameter handling alone because it does protect your database from Sql injection. However it is not the best way to handle parameters for Sql Server. ADO.net must guess at the datatype and it can guess wrong. For example, ADO.net sees a string as the value for a parameter. It assigns NVarChar as the datatype but the field in the database may actually be a VarChar. Now Sql Server must temporarily expand the field to an NVarChar to search for your parameter. You can see how inefficient that is. Then consider a number value of 6. Is it an integer or some type of float?
In the button code I used Integer.TryParse to validate the user input adding an Exit Sub to allow the user to correct the error.
Apparently the real problem was as commented by @Andrew Morton. I changed the names of the parameters. Many times joins are from primary key to foreign key but perhaps you have composite keys.
Data Access Class
Public Class SQLControl
Private ConStr As String = "Your connection string"
'Query Parameters
Public Params As New List(Of SqlParameter)
'This generates a blank sqlclient class with the deafult connection string
Public Sub New()
End Sub
'Allow connection string override
Public Sub New(connectionString As String)
ConStr = connectionString
End Sub
'Execute Query Sub
Public Function ExecQuery(query As String) As DataTable
'Params.Clear()
Dim DBDT = New DataTable
Using DBCon As New SqlConnection(ConStr),
DBCmd As New SqlCommand(query, DBCon)
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
Params.Clear()
DBCon.Open()
DBDT.Load(DBCmd.ExecuteReader)
End Using
Return DBDT
End Function
'Add Params
Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New SqlParameter(Name, Value)
Params.Add(NewParam)
End Sub
End Class
User Interface Code
Private Sql As New SQLControl("Your connection string")
Protected Sub ExecuteButton_Click(sender As Object, e As EventArgs) Handles ExecuteButton.Click
Dim StoreID As Integer
Dim TransID As Integer
If Not Integer.TryParse(StoreIDTextbox.Text, StoreID) Then
MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
Exit Sub
End If
If Not Integer.TryParse(TransactionIDTextbox.Text, TransID) Then
MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
Exit Sub
End If
Sql.AddParam("@Str_ID", StoreID)
Sql.AddParam("@Tran_ID", TransID)
Dim dt As DataTable
Try
dt = Sql.ExecQuery("SELECT H.Emp_ID, H.Cust_ID, D.*
FROM Transaction_Header H
INNER JOIN LN_Detail L ON (H.Str_ID = L.Str_ID AND H.Rgst_ID = L.Rgst_ID AND H.Tran_ID = L.Tran_ID)
INNER Join LN_Discount D ON (L.Str_ID = D.Str_ID AND L.Rgst_ID = D.Rgst_ID AND L.Tran_ID = D.Tran_ID AND L.Tran_LN_Num = D.Tran_LN_Num)
WHERE H.Str_ID = @Str_ID AND H.Tran_ID = @Tran_ID")
Catch ex As Exception
MessageBox.Show(ex.Message)
Exit Sub
End Try
GridView1.DataSource = dt
End Sub