时间戳

首页 > 解决方案 > 搜索功能失败

asp.net - 搜索功能失败

问题描述

我想在文本框中搜索数据。下面是我的代码。我试图搜索但没有任何反应。

If Not Me.IsPostBack Then Me.SearchPanelId() End If End Sub

Private Sub SearchPanelId()
    Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Using con As New SqlConnection(ConnectionString)
        Using cmd As New SqlCommand()
            Dim sql As String = "SELECT panelid, panelname, paneltype FROM PANEL_TABLE"
            If Not String.IsNullOrEmpty(TextBox1.Text.Trim()) Then
                sql += " WHERE panelid LIKE @panelid + '%'"
                cmd.Parameters.AddWithValue("@panelid", TextBox1.Text.Trim())
            End If
            cmd.CommandText = sql
            cmd.Connection = con
            Using sda As New SqlDataAdapter(cmd)
                Dim dt As New DataTable()
                sda.Fill(dt)
                GridView1.DataSource = dt
                GridView1.DataBind()
            End Using
        End Using
    End Using
End Sub


Protected Sub Search(sender As Object, e As EventArgs)
    Me.SearchPanelId()
End Sub

Protected Sub OnPaging(sender As Object, e As GridViewPageEventArgs)
    GridView1.PageIndex = e.NewPageIndex
    Me.SearchPanelId()
End Sub

标签： asp.netvb.net

解决方案

在开始创建对象之前进行验证。您需要检查该 ID 的数据类型是否有效。我猜这是一个整数类型，但检查你的数据库。如果我错了并且数据类型是 .VarChar，那么请查看第二个版本。:-) Like 关键字对数字字段没有任何意义。

不要使用 .AddWithValue 见http://www.dbdelta.com/addwithvalue-is-evil/ 和 https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop- using-addwithvalue-already/ 和另一个： https ://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications

不需要 DataAdapter。只需使用 DataTable 的加载方法。

Private Sub SearchPanelId()
    Dim IDValue As Integer
    Dim dt As New DataTable
    If String.IsNullOrEmpty(TextBox1.Text.Trim()) OrElse Not Integer.TryParse(TextBox1.Text.Trim, IDValue) Then
        Return
    End If
    Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Using cmd As New SqlCommand("SELECT panelid, panelname, paneltype FROM PANEL_TABLE WHERE panelid = @panelid", con)
            cmd.Parameters.Add("@panelid", SqlDbType.Int).Value = IDValue
            con.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    GridView1.DataSource = dt
    GridView1.DataBind()
End Sub

如果 Id 是 .VarChar

Private Sub SearchPanelId()
    Dim dt As New DataTable
    If String.IsNullOrEmpty(TextBox1.Text.Trim()) Then
        Return
    End If
    Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Using cmd As New SqlCommand("SELECT panelid, panelname, paneltype FROM PANEL_TABLE WHERE panelid Like @panelid", con)
            cmd.Parameters.Add("@panelid", SqlDbType.VarChar).Value = TextBox1.Text.Trim() & "%"
            con.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    GridView1.DataSource = dt
    GridView1.DataBind()
End Sub

推荐阅读