Visual Studio 2019.NET VB web应用程序SQL语法错误

Question

我想用文本框中的两个用户输入执行查询搜索，并在Viewgrid上显示结果，但我想在查询中插入的参数有问题，我得到了以下错误：

ExecQuery错误：
第1行："."附近的语法不正确
必须声明变量'@H'。

我是否实现了不正确的函数，或者是以不正确的方式执行，或者只是试图不正确地填充视图网格？如果有人能为我指明正确的方向，我将不胜感激。

这是我的SQLControl代码：

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

这是我执行查询的按钮：

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

Answer 1

在您的班级中：我已经将您的数据库对象移动到使用它们的方法中，以便可以关闭和处理它们。Using...End Using块为您完成此操作。

如果您需要记录计数，可从DataTable.Rows.Count获得

如果您不打算在类中处理异常，那么就不要将Try...End Try放在那里。拥有数据访问类的要点之一是将用户界面与数据源分离。在数据访问类中显示一个消息框，硬编码到用户界面的链接。你的类不应该绑定到Windows中可以显示消息框的桌面应用程序。假设你想在一个网络应用程序中使用你的类。你必须重写这门课。我将Try...End Try放在按钮事件(用户界面代码(中，您可以在其中显示消息框。

我没有考虑参数处理，因为它确实保护了数据库不受Sql注入的影响。但是，这不是处理SqlServer参数的最佳方式。ADO.net必须猜测数据类型，它可能会猜错。例如，ADO.net将字符串视为参数的值。它指定NVarChar作为数据类型，但数据库中的字段实际上可能是VarChar。现在，SqlServer必须将字段临时扩展为NVarChar以搜索参数。你可以看到这是多么的低效。然后考虑一个数值6。它是整数还是某种类型的浮点？

在按钮代码中，我使用Integer.TryParse验证用户输入，添加Exit Sub以允许用户更正错误。

显然，真正的问题正如@Andrew Morton所评论的那样。我更改了参数的名称。很多时候，连接是从主键到外键的，但也许您有复合键。

数据访问类

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

用户界面代码

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