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