-
December 9th, 2013, 05:38 PM
#1
Having problem with my program search function.
My program is a simple search based program. It searches input text from a user then search it from the database which consist of 1 table and 4 columns(name,email,contact,postcode). Searched record will display into the listview with details of the Name searched.
The problem now is, the search function is searching related text from user input through the database(all columns). eg : User searched for halen but it displayed halen and halena details. I want it to search exact same string as user key-in the textbox.
here's the code :
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 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) Or _
strRow(1).ToLower.Contains(strSearch) Or _
strRow(2).ToLower.Contains(strSearch) Or _
strRow(3).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
End Class
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|