vb.net VB.Net根据用户输入的文本框从sql数据库中检索数据

VB.Net retrieve data from sql database according to user input textbox

提问by Cara Richardson

I have created a SQL Database with a VB application. I have got a form that allows users to insert new records. Each new record has a unique ID number. I want the user to be able to search for a record using the ID number.

我用 VB 应用程序创建了一个 SQL 数据库。我有一个允许用户插入新记录的表单。每条新记录都有一个唯一的 ID 号。我希望用户能够使用 ID 号搜索记录。

I have got one page with a text box that allows them to insert their ID number. Then they click submit. So the database is queried; if the ID number does not exist they get a message box with an error. If the ID number does exist a new page is displayed and the record is displayed.

我有一个带有文本框的页面，允许他们插入他们的 ID 号。然后他们点击提交。于是查询了数据库；如果 ID 号不存在，他们会收到一个带有错误的消息框。如果 ID 号确实存在，则会显示一个新页面并显示记录。

I assume the database connection would begin on the first page when the user inputs their ID number. I have got two tables for orders so I need to query both tables. This is the code I have for the first page.

我假设当用户输入他们的 ID 号时，数据库连接将从第一页开始。我有两个订单表，所以我需要查询这两个表。这是我第一页的代码。

    Try

        Dim dbconnection As SqlConnection = New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Cara\Documents\Visual Studio 2012\Projects\Online Portal Solutions\Online Portal Solutions\Online Portal Solutions Database.mdf;Integrated Security=True")
        dbconnection.Open()
        Dim statement As String = "SELECT * FROM [JKPOrders] WHERE OrderNoID='" & txt_jkpfind.Text & "';"
        Dim com As SqlCommand = New SqlCommand(statement, dbconnection)
        Dim read As SqlDataReader = com.ExecuteReader

        Dim dbconnection2 As SqlConnection = New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Cara\Documents\Visual Studio 2012\Projects\Online Portal Solutions\Online Portal Solutions\Online Portal Solutions Database.mdf;Integrated Security=True")
        dbconnection2.Open()
        Dim statement2 As String = "SELECT * FROM [ClarkeOrders] WHERE OrderNoID='" & txt_jkpfind.Text & "';"
        Dim com2 As SqlCommand = New SqlCommand(statement2, dbconnection2)
        Dim read2 As SqlDataReader = com2.ExecuteReader

        If read.Read Then
            If txt_jkpfind.Text.ToString <> read("OrderNoID") Then
                jkpfindorderno = Val(txt_jkpfind.Text)
                Me.Hide()
                frm_Ecustjkpbookingsummary.Show()
            End If

        ElseIf read2.Read Then
            If txt_jkpfind.Text.ToString <> read("OrderNoID") Then
                jkpfindorderno = Val(txt_jkpfind.Text)
                Me.Hide()
                frm_Ecustjkpbookingsummary.Show()
            End If

        Else
            MessageBox.Show("no.", "No Entry",
                       MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

    Catch ex As Exception

    End Try

It identifies when a record does not exist and displays the message box but when the right ID number is input it doesn't do anything, what am I doing wrong?

它识别何时不存在记录并显示消息框，但是当输入正确的 ID 号时它什么也不做，我做错了什么？

Also, I have got a data adapter, data binder, etc on the following page that displays the record. Is this how I display the record and how do I do that?

另外，我在显示记录的下一页上有一个数据适配器、数据绑定器等。这是我显示记录的方式，我该怎么做？

回答by Skye MacMaster

Having two connections is unnecessary. You're using a mdf database file. Which is an access database file. I'm pretty certain Access only allows one connection at a time. So you're first query might be blocking the second one. See if it works by just creating one connection.

有两个连接是不必要的。您正在使用 mdf 数据库文件。这是一个访问数据库文件。我很确定 Access 一次只允许一个连接。所以你的第一个查询可能会阻止第二个查询。看看它是否可以通过创建一个连接来工作。

Also, all your database connections are local to this function and have nothing to do with your data bindings. You will have to show details about how you have your binding setup for us to point out what might be wrong with it.

此外，您的所有数据库连接都是该函数的本地连接，与您的数据绑定无关。您必须显示有关如何设置绑定的详细信息，以便我们指出它可能存在的问题。

Also, you should use paramaterized queries to prevent sql injection and other things. To parameterize the first query do this.

此外，您应该使用参数化查询来防止 sql 注入和其他事情。要参数化第一个查询，请执行此操作。

Dim statement As String = "SELECT * FROM [JKPOrders] WHERE OrderNoID=@OrderId;"
Dim com As SqlCommand = New SqlCommand(statement, dbconnection)
com.Parameters.Add(new SqlParameter("@OrderId", txt_jkpfind.Text))

回答by Vignesh Kumar A

Try like this

像这样尝试

 If read.Read Then
            If txt_jkpfind.Text.ToString <> read("OrderNoID").ToString() Then
                jkpfindorderno = Val(txt_jkpfind.Text)
                Me.Hide()
                frm_Ecustjkpbookingsummary.Show()
            End If

        ElseIf read2.Read Then
            If txt_jkpfind.Text.ToString <> read("OrderNoID").ToString() Then
                jkpfindorderno = Val(txt_jkpfind.Text)
                Me.Hide()
                frm_Ecustjkpbookingsummary.Show()
            End If

        Else
            MessageBox.Show("no.", "No Entry",
                       MessageBoxButtons.OK, MessageBoxIcon.Error)
 End If