JOIN DEVPPL

It looks like you are new here. If you want to get involved, click on Join below!



Having problem with my program search function.


K

keyboard_arrow_up
0
keyboard_arrow_down


First Post Mark as Spam kyleluke
Posts: 1 - Registered: 2013-12-09 16:40:38

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.

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






JOIN DEVPPL

It looks like you are new here. If you want to get involved, click on Join below!




MENU
Advertising