CAse SEnsitive and everything.

[paoi00]

i have 3 textboxes and 1 submit button :

1. Username
2. Secret Question
3. Secret Answer

when the user inputs their username , the Secret Question automatically appeared in the second textbox , and then put their answer in the third textbox.. just clicking the submit button the system will verifying if the user inputted it correctly and user will directly in the change password part of the form..

my problem is how username and secret answer becomes case sensitive based on what the user registers. and other problems will be stated as follows. :

here's my codes for usernametextbox :

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
    Dim uname As String
    Dim rUname As String = ""
    uname = Trim(TextBox1.Text)
    If TextBox1.TextLength < 20 Then

        con = New OleDbConnection("Provider= Microsoft.ACE.oledb.12.0; Data Source=F:\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
        Dim ewaaa As String = "Select * from EditAccount  where Username = '" & TextBox1.Text & "'"
        com = New OleDbCommand(ewaaa, con)
        con.Open()
        com.ExecuteNonQuery()
        rid = com.ExecuteReader
        While rid.Read()
            rUname = rid.Item("Username")
            TextBox4.Text = rid(5)
        End While
        rid.Read()
        If rid.HasRows Then
            TextBox4.Text = rid(5)

        Else
            MsgBox("Wrong username", MsgBoxStyle.Critical, "Warning")
        End If
    End If
End Sub

the problem with that codes every time i type a letter it shows the prompt.. until the correct username will type correctly.. there's an error : No data exists for the row/column.
but.. i have data in that row..

here's for submit button :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim upass As String
    Dim rPass As String = ""
    upass = Trim(TextBox2.Text)
    If TextBox1.Text = "" Or TextBox2.Text = "" Then
        MsgBox("Complete the form!", MsgBoxStyle.Information)
    ElseIf TextBox2.TextLength < 20 Then

        con = New OleDbConnection("Provider= Microsoft.ACE.oledb.12.0; Data Source=F:\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
        Dim ewaaa As String = "Select * from EditAccount  where Secret_Answer = '" & TextBox2.Text & "'"
        com = New OleDbCommand(ewaaa, con)
        con.Open()
        com.ExecuteNonQuery()

        rid = com.ExecuteReader
        While rid.Read()

            rPass = rid.Item("Secret_Answer")
        End While
        rid.Read()
        If rid.HasRows Then
            TextBox2.Text = rid(6)
            MsgBox("Your Answer is Correct", MsgBoxStyle.Information)
            MsgBox("Create your new Password. Make sure it is 8-12 Characters.", MsgBoxStyle.Information)
            PasswordTextBox.Enabled = True
            TextBox3.Enabled = True
            TextBox1.Enabled = False
            TextBox2.Enabled = False
        Else
            MsgBox("Your Answer is Wrong", MsgBoxStyle.Critical, "Warning")
        End If
    End If
End Sub

same problem with textboxusername , no data exist but there's data save in my database..

[Sahil89]

I didnt understand your problem exactly but If its about case sensetivity while comparing two strings, then you should convert them to upper or lower case first n then compare.

N the second problem u can solve by using timer of few seconds then run that code or using lostfocus event instead of change event.

[Reverend Jim]

If you want to compare and ignore case you can use

String.Compare(stringa,stringb,True)

The third parameter is True to ignore case, False otherwise. It returns

-1 if stringa < stringb
 0 if stringa = stringb
+1 if stringa > stringb

[paoi00]

@reverend jim -- i used to place the codes here .. but hthenn same error i encounter.. " NO DATA EXIST IN ROW/COLUMN" but thre's a record. please help me ..

 Dim uname As String
    Dim rUname As String = ""
    uname = Trim(TextBox1.Text)

    If TextBox1.TextLength < 20 Then

        con = New OleDbConnection("Provider= Microsoft.ACE.oledb.12.0; Data Source=F:\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
        Dim ewaaa As String = "Select * from EditAccount  where Username = '" & TextBox1.Text & "'"
        com = New OleDbCommand(ewaaa, con)
        con.Open()
        com.ExecuteNonQuery()
        rid = com.ExecuteReader
        While rid.Read()
            rUname = rid.Item("Username") <<<== ERROR
            String.Compare(uname, rUname, False)
        End While
        rid.Read()
        If rid.HasRows Then
            TextBox4.Text = rid(5) <<<=== error 

    End If
    End If

[vbDotMe]

Try just using SELECT * FROM EditAccount then your adapter will hold everything. Then use process of elimination to figure it out.

Try this:

    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String

    dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
    dbSource = "Data Source = F:\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb"
    con.ConnectionString = dbProvider & dbSource
    con.Open()

    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql = "SELECT * FROM EditAccount"
    da = New OleDb.OleDbDataAdapter(sql, con)
    da.Fill(ds, "Accounts")

    Dim Num = ds.Tables("Accounts").Rows.Count
    Dim User, Passcode, Answer
    dim MSG = ""

    For I = 0 To Num - 1

        User = ds.Tables("Accounts").Rows(I).Item(1)
        Passcode = ds.Tables("Accounts").Rows(I).Item(2)
        Answer = ds.Tables("Accounts").Rows(I).Item(3)

        Msg += User & " -- " & PassCode & " -- " & Answer & vbcrlf

    Next I
    con.Close()

    MsgBox(Msg)

Drop that into your code and see what you get.

[paoi00]

vbdotme -- i want to be my entry to be case sensitive.. your codes only shows every rows that satisfies your query. please help me

[vbDotMe]

I know, I was saying that it wouldn't be case sensitive, just that it would resolve the issue that you can't see any data. As For the other problem, VB.NET strings are case sensitive. Make a new project, add 2 textboxes and 1 button. Add this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    If TextBox1.Text = TextBox2.Text Then
        MsgBox("Yes")
    Else
        MsgBox("No")
    End If
End Sub

Make textbox2 read-only and the text say "Hello". If you enter "hello" or "HELLO" into textbox1 and push the button you'll recieve a "No". Only if you put exactly "Hello" you'll get a "Yes" reply.

[paoi00]

maybe in VB.net its case sensitive.. but when i compare the text from database to the text entered to the textbox its not case sensitive..

can you please help me?

[Reverend Jim]

If you want to make queries case sensitive in SQL server you can alter the particular column as follows:

ALTER TABLE test1 
    ALTER COLUMN UserName VARCHAR(50) 
    COLLATE Latin1_General_CS_AS 

In this case I have changed the default collation on UserName (which was deined originally as varchar(50). If you want to change it back use

ALTER TABLE test1 
    ALTER COLUMN UserName VARCHAR(50) 
    COLLATE SQL_Latin1_General_CP1_CI_AS

[paoi00]

If you want to make queries case sensitive in SQL server you can alter the particular column as follows:

ALTER TABLE test1
ALTER COLUMN UserName VARCHAR(50)
COLLATE Latin1_General_CS_AS
In this case I have changed the default collation on UserName (which was deined originally as varchar(50). If you want to change it back use

ALTER TABLE test1
ALTER COLUMN UserName VARCHAR(50)
COLLATE SQL_Latin1_General_CP1_CI_AS

i used ms access 2007 connected in vb.net .. how i could i do that?

[adam_k]

On your last question: You can't use collations with MS Access as far as I know.

I'd suggest you change the way you approach the whole thing and use a datatable to hold your usernames.
Using a datatable offers you solution to both your problems for the price of one.
a) Case Sensitivity by using the datatable.casesensitive = True (Read here: http://msdn.microsoft.com/en-us/library/system.data.datatable.casesensitive.aspx)
b) You don't have to query the db for every char typed. You'll load the data into the datatable once and filter the datatable using the .select(criteria) (Read about it here: http://msdn.microsoft.com/en-us/library/t5ce3dyt.aspx ). This way you don't get the delay of quering the db for every char and you use less bandwidth for a remote db.

As for the msgbox Wrong Username I really believe it needs to be removed from the Textbox1.TextChanged event and used when textbox1 has lost focus. There is no way you know if the user has finished entering the username other than waiting for him to leave the textbox.

Let me know if this was helpfull.

[paoi00]

i edited my codes with this :

Dim table As New DataTable

    Dim con As New OleDbConnection("Provider=Microsoft.ace.oledb.12.0;data source=C:\Users\user\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
    cmda = New OleDbCommand("Select*from EditAccount where Username='" + TextBox1.Text + "'", con)
    con.Open()
    rid = cmda.ExecuteReader()

    If table.CaseSensitive = True Then
        TextBox4.Text = rid(5)
    End If

whether i entered right or wrong username .. it does not produce output.. can you help me?