动态 SQL - 从 Oracle 检索

Question

我有一个带有按钮的表单，它应该从Oracle数据库中查找记录。我在同一表单上有三个文本框，如果文本与数据库字段中的值匹配，则 Datagrid 应向我显示此记录。这是我的代码：

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'connect to oracle DB
      Dim oradb As String = "Data Source=orcl;User Id=Lucky;Password=Example;"
        Dim conn As New OracleConnection(oradb)
        conn.Open()
        Dim SQL As String
        SQL = "SELECT * FROM MyTable WHERE 1=1"
        'SQL statement for 1st textbox
        If Not TxtName.Text = "" Then
            SQL = SQL & " AND USER_NAME =" & TxtName.Text
        End If
        'SQL statement for 2nd textbox
        If Not TxtSurname.Text = "" Then
            SQL = SQL & " AND USER_SURNAME ="  & TxtSurname.Text
        End If
        'SQL statement for 3rd textbox
        If Not TxtAddress.Text = "" Then
            SQL = SQL & " AND USER_ADDRESS=" & TxtAddress.Text
        End If
        'select SQL statements and retrieve data using ExecuteReader
        Dim cmd As New OracleCommand(SQL, conn)
        cmd.CommandType = CommandType.Text
        Dim dr As OracleDataReader = cmd.ExecuteReader()
        Dim dt As New DataTable
        dt.Load(dr)
        DataGridView1.DataSource = dt
        End Sub

我做错了什么，点击按钮时没有任何反应？

Answer 1

这是您可以尝试的方向的非测试示例：

Private Sub populateDataGridView()
        'connect to oracle DB
        Const connectionString As String = "Data Source=orcl;User Id=Lucky;Password=Example;"
        Using conn As New OracleConnection(connectionString)
            conn.Open()
            Using cmd As New OracleCommand()
                Dim SQL As String = "SELECT * FROM testtable "
                Dim conjunction As String = " Where "
                'SQL statement for 1st textbox
                If Not TxtName.Text.Length = 0 Then
                    SQL = String.Concat(SQL, conjunction, " USER_NAME like :username")
                    cmd.Parameters.Add(New OracleParameter("username", String.Concat("%", TxtName.Text, "%")))
                    conjunction = " and "
                End If
                'SQL statement for 2nd textbox
                If Not TxtSurname.Text.Length = 0 Then
                    SQL = String.Concat(SQL, conjunction, " user_surname  like :usersurname")
                    cmd.Parameters.Add(New OracleParameter("usersurname", String.Concat("%", TxtSurname2.Text, "%")))
                    conjunction = " and "
                End If
                'SQL statement for 3rd textbox
                If Not TxtAddress.Text.Length = 0 Then
                    SQL = String.Concat(SQL, conjunction, " user_address like :useraddress")
                    cmd.Parameters.Add(New OracleParameter("useraddress", String.Concat("%", TxtAddress.Text, "%")))
                End If
                'select SQL statements and retrieve data using ExecuteReader
                cmd.Connection = conn
                cmd.CommandText = SQL
                cmd.CommandType = CommandType.Text
                Dim dr As OracleDataReader = cmd.ExecuteReader()
                Dim dt As New DataTable
                dt.Load(dr)
                DataGridView1.DataSource = dt
            End Using
        End Using
    End Sub