[RESOLVED] Check If Name Exists In MS Access

[bentumkoitaba]

I know this might be something very simple but it has gotten me scratching my head for some minutes. I want to check if the user entered UserName already exists in MS Access, if it does, the given is rejected.

Here is what I have:

 
If DTR("UserName") <> TxtUserName.Text.ToString Then
   'If Username is available, save the information to the database
   MyQry = "INSERT INTO UsersTable(UserName,FullName,UserPassword) VALUES('" & TxtUserName.Text "','" & TxtFullName.Text & "','" & TxtConfirmPassword.Text & "')"
   MyCmd = New OleDbCommand(MyQry, Conn)
   With MyCmd
      .ExecuteNonQuery()
   End With
Close()
Else
   MsgBox(Prompt:="The provided UserName is not availble. Plese choose another one.", Buttons:=MsgBoxStyle.Exclamation, Title:="UserNameError")
End If

[jmcilhinney]

Please don't post code snippets with fat wads of leading whitespace. Why so many people think it's a great idea to cut off the whitespace from the first line and then leave it in all the others and make the code less readable for us as a result eludes me. Use the Alt key when selecting a block of text in VS to exclude the leading whitespace. If you aren't going to do that, at least make the effort to delete it from your post before submitting.

[jmcilhinney]

This question has been asked and answered many, many times before. Query the database for a matching record. If you get one back then there's obviously a matching record, so you don't add another one. If you don't get one back then there's not a matching record, so add away.

[topshot]

Use parameters to protect your code from SQL injection issues!

Use the Alt key when selecting a block of text in VS to exclude the leading whitespace.

Huh. In all these decades of using Windows, I either never heard of this block select function or forgot about it.

[Shaggy Hiker]

I never knew that, either. I don't have much white space in my code, and if I copy something from VS, I only copy the text. There are a bajillion shortcuts in Windows, I doubt anybody knows them all (though if anybody does, jmcilhinney would be my guess), but this one seems particularly odd/useless. Apparently, MS decided it was worth adding a shortcut for it, though.

[ChrisE]

hi,

you can set the field in the Access Table to ..No dupplicates
and then catch the Message as an error

[wes4dbt]

If DTR("UserName") <> TxtUserName.Text.ToString Then

If this is the line your talking about then it's an easy fix.

If DTR("UserName").ToString <> TxtUserName.Text Then

Because DTR("UserName") is an object, so if you want to compare it to a string value then you need to convert it to a string. Also, there is no reason to add ToString to a TextBox.Text value, ".Text" is always a string already.

This,

If DTR("UserName") <> TxtUserName.Text.ToString Then

Wouldn't even compile and run if you had Option Strict set to ON. I would suggest doing that, it will make catching these types of errors much easier.

[jmcilhinney]

In all these decades of using Windows, I either never heard of this block select function or forgot about it.

It's not a general Windows feature but a VS-specific feature. They presumably realised that copying a block of code without fat wads of leading whitespace would be useful.

[bentumkoitaba]

Thanks everyone for your contributions on this simple issue.

Thanks too that I've learnt about Alt key.

I got the solution of this question anyway, I think I was't thinking when I asked in the first place. Thanks anyway.

'Check if there is duplicate UserName in the Database
MyQry = "SELECT * FROM UsersTable WHERE  UserName = '" & TxtUserName.Text & "'"
MyCmd = New OleDbCommand(MyQry, Conn)
MyCmd.ExecuteNonQuery()
DTR = MyCmd.ExecuteReader
Select Case DTR.Read
    Case True
        MsgBox(Prompt:="The provided UserName is not availble. Plese choose another one.", Buttons:=MsgBoxStyle.Exclamation, Title:="UserNameError")
    Case Else
        'If Username is available, save the information to the database
        Dim DBUserName As String = DTR("UserName")
        Dim _UserName As String = TxtUserName.Text
        MyQry = "INSERT INTO UsersTable(UserName,FullName,UserPassword) VALUES('" & TxtUserName.Text & "','" & TxtFullName.Text & "','" & TxtConfirmPassword.Text & "')"
        MyCmd = New OleDbCommand(MyQry, Conn)
        With MyCmd
            .ExecuteNonQuery()
        End With
        Close()
End Select

[topshot]

It's not a general Windows feature but a VS-specific feature. They presumably realised that copying a block of code without fat wads of leading whitespace would be useful.

Actually it is a Windows feature according to what I found when I searched for it (I was not doing it correctly at first), and it works in Word (since at least Word 97), Notepad, Notepad++. It does not work in Chrome. It must have to do with the type of container the text is within.

[jmcilhinney]

Actually it is a Windows feature according to what I found when I searched for it (I was not doing it correctly at first), and it works in Word (since at least Word 97), Notepad, Notepad++. It does not work in Chrome. It must have to do with the type of container the text is within.

I just tested in Notepad and it didn't work for me. Did you have to do anything special to make it work?

[topshot]

Sorry, was typing faster than my brain. Only Notepad++ and Word, not Notepad or Wordpad (I rarely use those anyway).

[wes4dbt]

duplicate

[wes4dbt]

'Check if there is duplicate UserName in the Database
MyQry = "SELECT * FROM UsersTable WHERE  UserName = '" & TxtUserName.Text & "'"
MyCmd = New OleDbCommand(MyQry, Conn)
MyCmd.ExecuteNonQuery()
DTR = MyCmd.ExecuteReader
Select Case DTR.Read
    Case True
        MsgBox(Prompt:="The provided UserName is not availble. Plese choose another one.", Buttons:=MsgBoxStyle.Exclamation, Title:="UserNameError")
    Case Else
        'If Username is available, save the information to the database
        Dim DBUserName As String = DTR("UserName")
        Dim _UserName As String = TxtUserName.Text
        MyQry = "INSERT INTO UsersTable(UserName,FullName,UserPassword) VALUES('" & TxtUserName.Text & "','" & TxtFullName.Text & "','" & TxtConfirmPassword.Text & "')"
        MyCmd = New OleDbCommand(MyQry, Conn)
        With MyCmd
            .ExecuteNonQuery()
        End With
        Close()
End Select

You might think this code is working correctly but there are definite problems.

This code seem to do absolutely nothing,

MyCmd = New OleDbCommand(MyQry, Conn)
MyCmd.ExecuteNonQuery()

You should be using Parameters in your queries. Try your code and use "O'Brian" as the username.

There's no reason to be using a DataReader or a Select Statement.

Here is an simple example,

        Using con As New OleDbConnection(My.Settings.waterConnectionString)
            con.Open()
            Using cmd As New OleDbCommand("Select Count(*) from autonum WHERE Field1 = ?", con)
                cmd.Parameters.AddWithValue("?", Me.TextBox1.Text)
                Dim num As Integer = CInt(cmd.ExecuteScalar)
                If num > 0 Then
                    MessageBox.Show("Name already in Database")
                Else
                    cmd.CommandText = "INSERT INTO autonum (Field1) VALUES(?)"
                    num = cmd.ExecuteNonQuery
                    If num > 0 Then
                        MessageBox.Show("User Added")
                    End If
                End If
            End Using
        End Using