VB SQL语句未选择正确的行 [英] VB SQL statement not selecting the correct row

问题描述

我正在尝试使用SELECT语句在数据库中选择"一个人，但是它并没有选择正确的人，我也不知道为什么.

I'm trying to use a SELECT statement to "select" a person in my database and It doesn't select the correct person whatsoever, I'm not sure as to why either.

我正在使用访问数据库.

I'm using an access Database.

数据库连接代码:

Imports System.Data.OleDb

Module Database_Connection

Public provider As String 'This will tell VS what database source type to use.
Public datafile As String 'This will provide the file itself that VS will use.
Public connstring As String 'This is the connection string that will tie the Provider and Datafile together so that we can make a physical connection
Public myconnection As OleDbConnection = New OleDbConnection 'Set's the variable myconnection as a new Connection to the database using the OleDb type.
Public dr As OleDbDataReader 'This will be used to read data from the database.


Public Sub Access_Database()

    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    datafile = "Folly_Beach_Data.accdb"
    connstring = provider & datafile
    myconnection.ConnectionString = connstring

    Try

        myconnection.Open() 'Opens the connection to test it.

    Catch ex As Exception

        MessageBox.Show("Error" & vbCrLf & vbCrLf & "Original Error: " & ex.ToString)

        'This is an error that most likely many people will recieve on their computers. I noticed the problem a 
        'while ago and looked for a way to fix it. This is both the easiest and only method to correct the error stated below.
        'It doesn't force you to download anything, you have to select the option to do so.
        If MsgBox("If you received an error that states: " & vbCrLf _
                  & Quotes & "The microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." _
                  & Quotes & "Please press ok to install the database engine: ", MsgBoxStyle.OkCancel, _
                  "Error") = MsgBoxResult.Ok Then

            System.Diagnostics.Process.Start("http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734")
            'This opens the webpage to directly download the file. As soon as you press okay on the messagebox the file is
            'instantly ready for download.

        ElseIf MsgBoxResult.Cancel Then

            MessageBox.Show("Here is the link for future reference if you would like to download it at a later time: " _
                            & vbCrLf & vbCrLf & "http://bit.ly/19FWu09", "For later")
            'I case you are untrusting of the file or cannot download it at the present time, it gives a link for later installation

            ConnectionError = True 'For Description view "MyVariables"

            myconnection.Close() 'Closes the connection

        End If



    Finally

        'If myconnection.State = ConnectionState.Open Then
        '    MessageBox.Show("The database was successfully connected to", "Success", MessageBoxButtons.OK)
        'End If

        ConnectionError = False

        myconnection.Close()
        'Closes the connection so we can open at a later time. 
        'Trying to re-use or re-open a connection string will crash the progrm.

    End Try

End Sub

最终模块

这是我在代码中选择此人的地方:

And here is where I'm select the person in my code:

请注意，我正在检查电话号码和邮政编码，以查看该人是否是该人. (我知道这是不安全的.这是一个学校项目).因此，我在一开始就使用了两个SELECT语句来检查人员信息.而且它可以正常工作.如果电话号码是正确的，但邮编不是，则表明它不正确并且不会继续.我这样做就像嵌套的SELECT语句一样.

Note that I'm check the phone number as well as the zip code to see if that person is that person. (This is not secure, I realize. This is a school project). So i'm using both SELECT statements in the beginning to check that persons info. And it works correctly. If the phone # is correct but the zip is not it shows that it's not correct and doesn't continue. I do it as like a nested SELECT statement.

 Private Sub ReturningCheck()

    Dim Phone As String = Phonetxt.Text
    Dim Zip As String = ziptxt.Text

    GuestFound = False

    Try

        myconnection.Open()

        Dim str As String

        str = "SELECT * FROM Customers WHERE Customer_Phone_Number='" & Phone & "'"

        Dim cmd As OleDbCommand = New OleDbCommand(str, myconnection)

        dr = cmd.ExecuteReader

        If dr.Read Then

            str = "Select * FROM Customers WHERE Customer_Address_Zip='" & Zip & "'"

            cmd = New OleDbCommand(str, myconnection)

            dr = cmd.ExecuteReader

            If dr.Read Then

                GuestName = dr("Customer_Name")

                MessageBox.Show("Welcome back " & GuestName & ".")

                GuestFound = True

            Else

                MessageBox.Show("The Phone number Matches but the zipcode does not, please re-enter the zip code that you first signed up with.")
                ziptxt.Focus()
                ziptxt.SelectAll()

            End If

        Else

            MessageBox.Show("That phone number does not exist in our records please re-enter the phone number in the format of 8001231234")
            Phonetxt.Focus()
            Phonetxt.SelectAll()

        End If

    Catch ex As Exception

        MessageBox.Show("There was an error retrieving your information from the database" _
                         & vbCrLf & vbCrLf & "Original Error: " & ex.ToString, _
                        "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally

        myconnection.Close()

    End Try

End Sub

我尝试在数据库中执行姓氏"，但是它返回了姓氏的名字，那么这有什么问题呢?任何帮助将不胜感激.

I try and do the Last person in the database but it returns the name of the second person, So what is wrong with it? Any help will be greatly appreciated.

推荐答案

而不是连续调用两个SQL语句，而是将条件合并到单个SQL语句的where子句中，如下所示:

Instead of calling two SQL Statements in a row, combine the conditions in the where clause of a single SQL statement, like this:

str = "SELECT * FROM Customers WHERE Customer_Phone_Number='" & Phone & _
      "' AND Customer_Address_Zip='" & Zip & "'"

如以下建议，您应修复代码以确保其免受SQL攻击.简而言之，只要您在用户输入的字段中插入字符串，就需要对其进行清理.实现此目的的最佳方法是使用参数化查询.请搜索"VB中的参数化查询"，您会发现很多示例.

As suggested below, you should fix your code to make sure it is protected against SQL attacks. In short, any time you insert a string from a field entered by users you need to sanitise it. The best way to achieve this is by using a parameterized query. Please search "parameterized query in VB", you will find a lot of examples.

这篇关于VB SQL语句未选择正确的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！