Click here to Skip to main content
13,594,672 members
Rate this:
Please Sign up or sign in to vote.
See more:
hi experts,

i have this code working for log in, but i would need some help to make sure if username that already exists in the DB table cannot be used to register a new user.

any help??? thanks!

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim Result As String
        Result = "Account Created" & vbCrLf
        Result = Result & "UserName: " & txtFName.Text & vbCrLf
        Result = Result & "Gender: " & ComboBox1.SelectedItem
        btnSubmit.Enabled = False

        Dim insertCommand As SqlCeCommand = ssceconn.CreateCommand()
        insertCommand.CommandText = "Insert Into People (user, l_name,p_name) Values (?,?,?)"
        insertCommand.Parameters.Add(New SqlCeParameter("user", SqlDbType.NText, 50))
        insertCommand.Parameters.Add(New SqlCeParameter("l_name", SqlDbType.NText, 50))
        insertCommand.Parameters.Add(New SqlCeParameter("p_name", SqlDbType.NText, 50))
        insertCommand.Parameters("user").Value = txtFName.Text
        insertCommand.Parameters("l_name").Value = txtLName.Text
        insertCommand.Parameters("p_name").Value = txtPName.Text

        If txtFName.Text = "" Or txtLName.Text = "" Or txtPName.Text = "" And ComboBox1.SelectedItem = "" Then

            MsgBox("Please fill in the form")
            txtPName.Text = ""
            txtLName.Text = ""

        ElseIf txtLName.Text = txtPName.Text And ComboBox1.SelectedItem = ("Male") Or ComboBox1.SelectedItem = ("Female") Then

            txtFName.Text = ""
            txtPName.Text = ""
            txtLName.Text = ""

        ElseIf MsgBox("Wrong Password Please retype or Please fill in your gender") Then
                txtPName.Text = ""
                txtLName.Text = ""

        End If

        btnSubmit.Enabled = True

    End Sub
Posted 11-Jan-13 2:58am
OriginalGriff 11-Jan-13 10:33am
Answer updated
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

I think you first need to look at the code you already have - it has flaws.

Why are you inserting data into the database and then checking to see if it is valid? all that does is fill your database with rubbish, that will prevent the user creating his account.
Suppose he enters a user but nothing else. No other user has the same value.
You insert the data into the db, and then tell him it is wrong.
He corrects it, and tried again - but now the user value is in use, so any code you add will reject his data - presumably after it has been added again.

Do your checking first - validity first, then existing value.

Checking tha actual existing value is just a simple case of a slightly different query:
SELECT COUNT(*) from People WHERE [user]=@USER
and setting the @USER parameter in much the same way you do above. Then issue a ExecuteScalar and check the return value. if it is non-zero, then records exist already.

Dim selectCommand As SqlCeCommand = ssceconn.CreateCommand()
       selectCommand.CommandText = "SELECT COUNT(*) FROM People WHERE f_name = '" & txtFName.Text & "'"
       Dim myReader As SqlCeDataReader = selectCommand.ExecuteReader

issit like correct??

Don't post as a solution, it means you send an email to people - use the Comment or Reply systems instead.

It will work, but it's a bad idea for two reasons:
Firstly, concatenating strings leaves you vulnerable to accidental or deliberate SQL injection attacks which can damage or destroy your database. Use parameterised queries as you did in your original code.
Secondly, there is no need to use a DataReader - you are only interested in a single integer value, so use the ExecuteScalar method instead, and it returns the one and only value directly:
Dim selectCommand As SqlCeCommand = ssceconn.CreateCommand()
selectCommand.CommandText = "SELECT COUNT(*) FROM People WHERE f_name = @FN"
selectCommand.Parameters.AddWithValue("@FN", txtFName.Text)
Dim existingUsers as Integer = selectCommand.ExecuteScalar
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

If you want that the user names (column user) are unique all the time, do not enforce this by yourself. Let the database handle it.

In order to do this define the column as a primary or unique key. I would think a unique key is more appropriate in this case since personally I would create a surrogate key as a primary key (not currently existing in your table).

But to define the user name as unique, you can modify the table like this:

For more information about unique constraint, see UNIQUE Constraints[^]

Now when the unique constraint exists, if you try to add the same user the second time, the SQL command will throw an error. Because of this you need to wrap the execution into a try..catch block.

Inside the catch you can check whether the error is because of the unique constraint or something else. If it's caused by the constraint, simply inform the user.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web04-2016 | 2.8.180621.3 | Last Updated 11 Jan 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100