Record Checking Problem

[Naveed_786]

Hi all,

I am trying to check a record in database before inerting a new record here is my code but problem is when i enter a names first alphabet it imediatly populate massage please help me i want to check it after entering whole name thanks in advance.

Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
        Try
            Dim cnString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\Payment.mdb;"
            Dim sqlQRY As String = "SELECT COUNT(*) AS numRows FROM CustomerInformation WHERE CustomerName = '" & TextBox1.Text & "'"
            Dim queryResult As Integer
            Dim conn As OleDbConnection = New OleDbConnection(cnString)
            conn.Open()
            Dim com As New OleDbCommand(sqlQRY, conn)
            queryResult = com.ExecuteScalar()
            conn.Close()
            If queryResult > 0 Then
                MessageBox.Show("Already Exists!", "ALI ENTERPRISES", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TextBox1.Text = ""
                Exit Sub
            End If
        Catch ex As Exception
            MessageBox.Show("Customer found" + ex.Message)
        End Try
    End Sub

[Oxiegen]

If the customers full name is stored in one single string, then what you have is perfectly fine.
However, you might wanna consider performing the check in all lower case.
Like so:

Dim sqlQRY As String = "SELECT COUNT(*) AS numRows FROM CustomerInformation WHERE LOWER(CustomerName) = '" & TextBox1.Text.ToLower() & "'"

But if name and surname are separate entities, then you need to do this:

Dim sqlQRY As String = "SELECT COUNT(*) AS numRows FROM CustomerInformation WHERE LOWER(CustomerName) = '" & TextBox1.Text.ToLower() & "' AND LOWER(CustomerSurname) = '" & SurnameTextBox.Text.ToLower() & "'"

[Naveed_786]

It gives me following error "Customerfound errorinfo.getdescription failed with E_Fail(0x80004005) please help.

[Oxiegen]

That is one of the most undescriptive error messages I've ever seen.

But you can probably eliminate the problem if you change LOWER() to LCASE(), , but because I don't know if you are using SQL Express or MS Access as a database, you just have to try your luck with the trial-and-error approach.

Also, I surely hope that you just didn't copied my snippet and thought it would work.
I have no idea how you store a customers name, either in one single column or in two.

[Naveed_786]

I have saved customer name in single column and iam using ms access database.

[Oxiegen]

Than LCASE() is the way to go.

[Naveed_786]

I have tried it but not working i have put the code on leave event it is working.