vb log in form with database user exists

Question

0.00/5 (No votes)

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!

VB

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
        ssceconn.Open()
        insertCommand.ExecuteNonQuery()
        ssceconn.Close()

        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

            MsgBox(Result)
            Form2.Show()
            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

dawnight

Add a Solution

Comments

OriginalGriff 11-Jan-13 10:33am

Answer updated

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2013-01-11T05:41:00

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:

SQL

ALTER TABLE People ADD CONSTRAINT UNQ_People_User UNIQUE ([user]);

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.

OriginalGriff · Answer 2 · 2013-01-11T03:34:00

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:

SQL

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.

[FROM THE OP: POSTED AS A SOLUTION]

VB

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:

VB

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

vb log in form with database user exists

2 solutions

Solution 3

Solution 1

Add your solution here

Preview 0