我试图使搜索按钮在vb.net从NTable与Id，名称，年龄.我得到错误在sda.fill(dt)(错误:)任何帮助将

Question

Private Function search1() As DataTable
Dim query1 As String = "select Id,Name,Age from NTable"
query1 &= "WHERE Id Like '%' +@parm1+ '%' "
query1 &= "OR Name Like '%' +@parm1+ '%' "
query1 &= "OR Age Like '%' +@parm1+ '%' "
query1 &= "OR @parm1=''"
Dim con1 As String = "Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=C:UsersAcer Nitro 5Documentscheck.mdf;Integrated Security=True;Connect Timeout=30"
Using conn As SqlConnection = New SqlConnection(con1)
Using cmd As SqlCommand = New SqlCommand(query1, conn)
cmd.Parameters.AddWithValue("@parm1", TextBox1.Text.Trim())
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function

错误:https://i.stack.imgur.com/hSut2.png

为什么在sda.fill(dt)中出现错误

Answer 1

下面展示了如何在对SQL Server数据库执行查询时使用LIKE的参数。

注意:在下面的代码中，将表名更改为您的表名。

Public Function GetData(searchStr As String) As DataTable
Dim dt As DataTable = New DataTable()
Dim query As String = "SELECT Id, Name, Age from Employee where Id LIKE @search OR Name like @search OR Age LIKE @search"
Debug.WriteLine(query)
Using con As SqlConnection = New SqlConnection(connectionStr)
'open
con.Open()
Using cmd As SqlCommand = New SqlCommand(query, con)
cmd.Parameters.Add("@search", SqlDbType.VarChar).Value = $"%{searchStr}%"
Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim numRowsAffected As Integer = da.Fill(dt)
Debug.WriteLine($"numRows: {numRowsAffected}")
End Using
End Using
End Using
Return dt
End Function

尽管上面的代码测试和似乎工作,我建议重新编写代码，以便传递正确的数据类型。如果数据库数据类型为整数，则指定SqlDbType.Int并传递一个integer值。

---

表定义如下:

Create table Employee(Id int Identity (1,1),
Name varchar(75),
Age int,
CONSTRAINT PK_Test_Id Primary Key(Id))

资源

---

:

• 插入字符串(Visual Basic Reference)