vb.net 如何在 Visual Studio VB 代码中使用文本框和搜索按钮进行数据库搜索

How to do a database search using a textbox and a search button in visual studio VB Code

提问by BiohazzardXIII

I want to type something in my txtSearchCriteriatextbox and when I hit the Perform Search button it displays all the records it finds into the other textboxes I have in my form. I am using a localdb file with a dataset in Visual Studio 2012. So far I have everything working except the search button. Here is my entire code in my project.

我想在我的txtSearchCriteria文本框中输入一些内容，当我点击执行搜索按钮时，它会将它找到的所有记录显示到我的表单中的其他文本框中。我在 Visual Studio 2012 中使用带有数据集的 localdb 文件。到目前为止，除了搜索按钮之外，我的所有工作都正常工作。这是我项目中的全部代码。

'Import Namespaces for SQL
 Imports System.Data
 Imports System.Data.SqlClient

Public Class Form1 Dim objCurrencyManager As CurrencyManager

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'Loads data into the 'CustomersDataSet.Customers' table.
    Me.CustomersTableAdapter.Fill(Me.CustomersDataSet.Customers)
    'Controls Record Number Movement
    objCurrencyManager = CType(Me.BindingContext(CustomersBindingSource), CurrencyManager)
    'Display the current record number 
    Position()
    'Display a tool tip at the bottom of screen
    lblToolStripLabel1.Text = "Ready To Compute Data"

End Sub

Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
    'Used try catch block to validate all fields to ensure proper use of form and to display error if required fields are missing user imput
    Try
        Me.Validate()
        Me.CustomersBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.CustomersDataSet)
        lblToolStripLabel1.Text = "Record Saved" 'Display that record has been saved 
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    Position()
End Sub

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
    'code to update an existing record and display that redord was updated successfully
    Try
        Me.Validate()
        Me.CustomersBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.CustomersDataSet)
        lblToolStripLabel1.Text = "Record Updated" 'display record updated
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    Position()
End Sub


Private Sub dtnDelete_Click(sender As Object, e As EventArgs) Handles dtnDelete.Click
    ' Removes the current record and displays the new position in the database
    Me.CustomersBindingSource.RemoveCurrent()
    Position()
End Sub

Private Sub btnMoveFirst_Click(sender As Object, e As EventArgs) Handles btnMoveFirst.Click
    'Moves the current record to the begining of the dataset. The First Record
    Me.CustomersBindingSource.MoveFirst()
    Position()
End Sub

Private Sub btnMovePrevious_Click(sender As Object, e As EventArgs) Handles btnMovePrevious.Click
    'moves to the previous record and displays the position
    Me.CustomersBindingSource.MovePrevious()
    Position()
End Sub

Private Sub btnMoveNext_Click(sender As Object, e As EventArgs) Handles btnMoveNext.Click
    'Moves to the next record in the dataset
    Me.CustomersBindingSource.MoveNext()
    Position()
End Sub

Private Sub btnMoveLast_Click(sender As Object, e As EventArgs) Handles btnMoveLast.Click
    'Moves to the last Record in the Dataset
    Me.CustomersBindingSource.MoveLast()
    Position()
End Sub

Private Sub Position()
    'This code displays the positio of the current record the user is viewing and shows it out of the number of recors that are held in the dataset 
    txtRecordPosition.Text = objCurrencyManager.Position + 1 & " Of " & objCurrencyManager.Count()
End Sub

Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
    'Clears the form for user imput, Created a new record and adds the current date to CustomerDate field and adds the next CustomerNumber available.
    Me.CustomersBindingSource.AddNew()
    txtCustomerNumber.Text = Me.CustomersBindingSource.Count + 1
    txtCustomerDate.Text = Date.Today.ToShortDateString
End Sub

Private Sub btnPerformSort_Click(sender As Object, e As EventArgs) Handles btnPerformSort.Click
    'Code to Sort by different field within the dataset
    Select Case cboField.SelectedIndex
        Case 0 'CustomerNumber
            CustomersBindingSource.Sort = "CustomerNumber"
        Case 1 'First Name
            CustomersBindingSource.Sort = "FirstName"
        Case 2 'Last Name
            CustomersBindingSource.Sort = "LastName"
        Case 3 'City
            CustomersBindingSource.Sort = "City"
        Case 4 'Province
            CustomersBindingSource.Sort = "Province"
    End Select

    btnMoveFirst_Click(Nothing, Nothing)
    lblToolStripLabel1.Text = "Records Sorted"

End Sub

Private Sub btnPerformSearch_Click(sender As Object, e As EventArgs) Handles btnPerformSearch.Click
    'This will take the user imput from the txtSearchCriteria textbox and return all records that have the criteria 

End Sub
End Class

回答by

Here is a sample to query database assuming you are connecting to sql server.

这是假设您正在连接到 sql server 查询数据库的示例。

Private Sub BindGrid()
Dim constr As String = ConfigurationManager.ConnectionStrings("<your connection string name>").ConnectionString
Using con As New SqlConnection(constr)
    Using cmd As New SqlCommand()
        cmd.CommandText = "SELECT ContactName, City, Country FROM Customers WHERE ContactName LIKE '%' + @ContactName + '%'"
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@ContactName", txtSearch.Text.Trim())
        Dim dt As New DataTable()
        Using sda As New SqlDataAdapter(cmd)
            sda.Fill(dt)
            gvCustomers.DataSource = dt
            gvCustomers.DataBind()
        End Using
    End Using
End Using
End Sub

To explain at first stepwe are declaring connection string then we are opening SqlConnectionobject and then we are querying the database table ContactName based on the txtSearch value inputted.Next we are filling the dataset and binding the grid.

为了解释第一步，我们声明连接字符串，然后打开SqlConnection对象，然后根据输入的 txtSearch 值查询数据库表 ContactName。接下来我们填充数据集并绑定网格。

I suggest you to read this msdn articleon sql connection.

我建议你阅读这篇关于 sql 连接的msdn 文章。