0

I'm having problem with my search function which now search anything related to letter or number. I want it search an exact match only from 1 column such as a name and show details of it.

here's my code

Imports System.IO
Imports System.Text
Imports System.Data.OleDb
Imports System.Security.Cryptography


Public Class Form1
Public Const ENCRYPTKEY As String = "ABCDE12345"

' definition of public variables
' mostly involve database connection
Public dbConn As New OleDbConnection
Public dbCmd As New OleDbCommand
Public dbRdr As OleDbDataReader
Public strSQL As String

' definition of constants
Public DATABASE_FILE As String = ""
Public CONN_STR As String = ""

' functions
Sub ConnectDB()

    If dbConn.State = ConnectionState.Open Then dbConn.Close()

    If DATABASE_FILE = "" Then
        MsgBox("Please select a database.", vbOKOnly + vbExclamation, "Error")
        Exit Sub
    End If

    Try
        dbConn = New OleDbConnection
        With dbConn
            .ConnectionString = CONN_STR
            .Open()
        End With
        'MsgBox("Database successfully opened.", vbOKOnly + MsgBoxStyle.Information, "Success")
    Catch ex As Exception
        MsgBox(ex.Message, vbOKOnly + vbExclamation, "Error")
    End Try


End Sub


Function CheckEncrypted() As Boolean

    Dim cStatus As Boolean = False

    If dbConn.State <> ConnectionState.Open Then
        Return False
    End If

    Dim dbCm As New OleDbCommand
    Dim dbDr As OleDbDataReader

    dbCm.Connection = dbConn
    dbCm.CommandText = "select * from tablestatus"
    dbDr = dbCm.ExecuteReader
    dbDr.Read()

    If dbDr("status") = 1 Then
        cStatus = True
    Else
        cStatus = False
    End If
    dbDr.Close()
    dbDr = Nothing

    Return cStatus

End Function


Private Shared Function Encrypt(ByVal strText As String, ByVal strEncrKey As String) As String
    Dim byKey() As Byte = {}
    Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}

    Try
        byKey = System.Text.Encoding.UTF8.GetBytes(Microsoft.VisualBasic.Left(strEncrKey, 8))
        Dim des As New DESCryptoServiceProvider()
        Dim inputByteArray() As Byte = Encoding.UTF8.GetBytes(strText)
        Dim ms As New MemoryStream()
        Dim cs As New CryptoStream(ms, des.CreateEncryptor(byKey, IV), CryptoStreamMode.Write)
        cs.Write(inputByteArray, 0, inputByteArray.Length)
        cs.FlushFinalBlock()
        Return Convert.ToBase64String(ms.ToArray())
    Catch ex As Exception
        Return ex.Message
    End Try

End Function


Private Shared Function Decrypt(ByVal strText As String, ByVal sDecrKey As String) As String
    Dim byKey() As Byte = {}
    Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
    Dim inputByteArray(strText.Length) As Byte

    Try
        byKey = System.Text.Encoding.UTF8.GetBytes(Microsoft.VisualBasic.Left(sDecrKey, 8))
        Dim des As New DESCryptoServiceProvider()
        inputByteArray = Convert.FromBase64String(strText)
        Dim ms As New MemoryStream()
        Dim cs As New CryptoStream(ms, des.CreateDecryptor(byKey, IV), CryptoStreamMode.Write)

        cs.Write(inputByteArray, 0, inputByteArray.Length)
        cs.FlushFinalBlock()
        Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
        Dim a As String = encoding.GetString(ms.ToArray())
        Return encoding.GetString(ms.ToArray())
    Catch ex As Exception
        Return ex.Message
    End Try
End Function

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    DATABASE_FILE = Application.StartupPath & "\testsearch.mdb"
    CONN_STR = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DATABASE_FILE & ";Jet OLEDB:Database Password=abcde12345;"

    ConnectDB()

    'With OpenFileDialog1
    '.Filter = "Microsoft Access databases (*.mdb)|*.mdb"
    '.FilterIndex = 1
    'End With

    ' initialize listview
    With ListView1
        .Items.Clear()

    End With

End Sub


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    'check if text box is filled

    If TextBox1.Text = "" Or TextBox1.TextLength <= 0 Then
        MsgBox("Please enter text in the input string.", vbOKOnly + vbExclamation, "Error")
        Exit Sub
    End If

    If dbConn.State <> ConnectionState.Open Then
        MsgBox("Database is not connected. Fix the search string and try again.", vbOKOnly + vbExclamation, "Error")
        Exit Sub
    End If




    Dim strEncrypt As String = Encrypt(TextBox1.Text, "ABCDE12345")
    'MsgBox(strEncrypt)

    dbCmd = New OleDbCommand
    dbCmd.CommandText = "insert into tabletest (field1) values (" & Chr(34) & strEncrypt & Chr(34) & ")"
    dbCmd.Connection = dbConn
    dbCmd.ExecuteNonQuery()

    MsgBox("Data inserted into database.", vbOK + vbInformation, "Success")


End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

    OpenFileDialog1.ShowDialog()

End Sub

Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk

    DATABASE_FILE = OpenFileDialog1.FileName
    CONN_STR = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DATABASE_FILE & ";"
    TextBox2.Text = DATABASE_FILE

End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

    ConnectDB()

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    ListView1.Items.Clear()


    If TextBox4.Text = "" Or TextBox4.TextLength <= 0 Then
        'MsgBox("Please enter text in the search string.", vbOKOnly + vbExclamation, "Error")
        Exit Sub
    End If

    If dbConn.State <> ConnectionState.Open Then
        MsgBox("Database is not connected. Fix the search string and try again.", vbOKOnly + vbExclamation, "Error")
        Exit Sub
    End If

    If CheckEncrypted() = False Then
        MsgBox("Database is not encrypted. Cannot continue.", vbOKOnly + vbExclamation, "Error")
        Exit Sub
    End If

    Dim strSearch As String

    strSearch = TextBox4.Text.ToLower

    dbCmd = New OleDbCommand
    dbCmd.Connection = dbConn
    dbCmd.CommandText = "select * from tabletest"

    Dim rs As OleDbDataReader = dbCmd.ExecuteReader
    While rs.Read

        Debug.Print(rs("field1") & " " & Decrypt(rs("field1"), ENCRYPTKEY))

        Dim strRow(4) As String
        If Not IsDBNull(rs("field1")) Then strRow(0) = Decrypt(rs("field1"), ENCRYPTKEY) Else strRow(0) = ""
        If Not IsDBNull(rs("field2")) Then strRow(1) = Decrypt(rs("field2"), ENCRYPTKEY) Else strRow(1) = ""
        If Not IsDBNull(rs("field3")) Then strRow(2) = Decrypt(rs("field3"), ENCRYPTKEY) Else strRow(2) = ""
        If Not IsDBNull(rs("field4")) Then strRow(3) = Decrypt(rs("field4"), ENCRYPTKEY) Else strRow(3) = ""

        If _
            strRow(0).ToLower.Contains(strSearch) Then


            Dim itm As ListViewItem
            itm = New ListViewItem(strRow)
            ListView1.Items.Add(itm)

        End If

    End While
    rs.Close()
    rs = Nothing

End Sub

Private Sub TextBox4_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox4.KeyDown
    If e.KeyCode = Keys.Enter Then
        Button2.PerformClick()
        TextBox4.SelectAll()
    End If
End Sub

End Class

Edited by zarco

2
Contributors
1
Reply
14
Views
3 Years
Discussion Span
Last Post by Minimalist
0

just glancing over the code it might be better to use the string.equal method or string.compare?

    If _
                strRow(0).ToLower.Contains(strSearch) Then
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.