Click here to Skip to main content
11,704,315 members (65,112 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB Visual-Studio
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
        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
Comments
OriginalGriff at 11-Jan-13 10:33am
   
Answer updated
Rate this: bad
good
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.


[FROM THE OP: POSTED AS A SOLUTION]
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
  Permalink  
v2
Rate this: bad
good
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:
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.
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 740
1 OriginalGriff 520
2 Maciej Los 355
3 Andy Lanng 284
4 CPallini 275
0 OriginalGriff 8,824
1 Sergey Alexandrovich Kryukov 8,247
2 CPallini 5,197
3 Maciej Los 4,726
4 Mika Wendelius 3,606


Advertise | Privacy | Mobile
Web04 | 2.8.150819.1 | Last Updated 11 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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