VB.net User Registration problem with SQL service database!! Please HELP!!

[megaman21]

Hey everyone, kinda new to all this...really need some help:

Ok so I want to creat a log in and registration form in VB connecting to a sql database.

Databse Name: Users
Table Name: UserDetails
Table Fields: ID, Username, Password

The ID field was specifed as primary and self incrementing by 1 and the table has one username and password (being Id: 1, Username: peter, Password: admin).

The Login form works, it checks to see if the user exists, if they do, they gain access, if not a message box is displayed.

The Registration form however does not and I have no idea why, its probably something simple but yeah, hence the reason Im here!

When I register I can add a user fine, and it wont allow me to add the same user, but when I look at the table, it is not updated :S and I have no idea why!!

Even after registering the user closing the application and starting again, say I registered the following:

Username: Fred
Password: 123

Then I login with these credentials, it works. When I close the program and reopen the program, it still works and will not allow me to register a new user being Fred, 123.

However after 3 or 4 minutes when I try again, the user no longer exisits and I can re-register with set credentials?!?!!!

Weird :(

Please Help :icon_cry: The code is shown below (1st the login form that works, second the registration that doesnt).

Public Class Login

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click


        Dim connection As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Users.mdf;Integrated Security=True;User Instance=True")
        Dim CheckDetailsCommand As New SqlClient.SqlCommand
        Dim adaptor As New SqlClient.SqlDataAdapter
        Dim dataset As New DataSet

        CheckDetailsCommand.CommandText = "SELECT * FROM [UserDetails] WHERE username='" + txtUsername.Text + "'AND password='" + txtPassword.Text + "';"
        connection.Open()

        CheckDetailsCommand.Connection = connection

        adaptor.SelectCommand = CheckDetailsCommand
        adaptor.Fill(dataset, "0")

        Dim count = dataset.Tables(0).Rows.Count

        If count > 0 Then
            Me.Close()
        Else
            MsgBox("Incorrect Login Details Entered: Please Check Username & Password", MsgBoxStyle.Critical)
            txtPassword.Clear()
            txtUsername.Clear()
        End If

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

End Class

Public Class Register
   

    Private Sub btnAccountOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAccountOK.Click

        Dim connection As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Users.mdf;Integrated Security=True;User Instance=True")
        Dim CheckDetails As New SqlClient.SqlCommand
        Dim adaptor As New SqlClient.SqlDataAdapter
        Dim dataset As New DataSet
        Try
            CheckDetails.CommandText = "SELECT * FROM [UserDetails] WHERE username='" + txtUsername.Text + "'AND password='" + txtPassword.Text + "';"
            connection.Open()

            CheckDetails.Connection = connection

            adaptor.SelectCommand = CheckDetails
            adaptor.Fill(dataset, "0")

            Dim count = dataset.Tables(0).Rows.Count

            If count > 0 Then
                MsgBox("This user already exisits: Please use different credentials", MsgBoxStyle.Critical)
                txtPassword.Clear()
                txtUsername.Clear()
            Else
                Dim AddDetails As New SqlClient.SqlCommand
                AddDetails.CommandText = "INSERT INTO [UserDetails] (Username, Password)VALUES ('" + txtUsername.Text + "','" + txtPassword.Text + "')"
                Dim data As SqlClient.SqlDataReader
                Dim adapter2 As New SqlClient.SqlDataAdapter

                AddDetails.Connection = connection
                adapter2.InsertCommand = AddDetails
                data = AddDetails.ExecuteReader
                MsgBox("You've Registered!")
            End If
        Finally
            Me.Close()
        End Try
    End Sub

[adam_k]

I am betting that you can register the same user, but not with the same password.
You shouldn't include the password in the query that checks if the user exists. Usernames should be unique, not username/password.
If I were you I would enforce this from the db.

[megaman21]

Thanks for your reply, I can't register the same user, with the same password\or a differnet one. As the username is unique. I have taken the password out of the queery that checks to see if the user exists but I still have nothing and I have no idea on how to get the databse table to update.

Done it in php, works fine, but for some reason, I cannot do it in VB!

It simply wont update the UserDetails table in the Users.mdf database.

Its doing my head in!

[adam_k]

Was posting from my mobile earlier, and didn't get a good look to your code.

Try changing the

data = AddDetails.ExecuteReader

to

data = AddDetails.ExecuteNonQuery

By the way, what do you mean Update?

I've just read your original post again and you say:
"When I register I can add a user fine, and it wont allow me to add the same user, but when I look at the table, it is not updated and I have no idea why!! "

If the user is added, what do you mean update?

[megaman21]

Ok I tried changing:

data = AddDetails.ExecuteReader

to

data = AddDetails.ExecuteNonQuery

but it gave me an error:

Error 1 Value of type 'Integer' cannot be converted to 'System.Data.SqlClient.SqlDataReader'

And in answer to your question, I mean update in the sense that when I start the program in the Users database in the UserDetails table the following is evident:

ID Username Password
1 peter admin

When I check to see if the login form works (entering the above username and password) it works, and if I enter any other form of username/password it displays a message box stating wrong username password etc...

BUT...when I try and add someone else say [username: fred] [password: bob] the table SHOULD appear like this:

ID Username Password
1 peter admin
2 fred bob

However for some reason, it doesnt actually register the user in the table :( But while running the program, it will act as if the details have been inserted and the user has been registered :S (i.e fred can login, and fred cannot be re-registered) but after stopping the program and looking at the table again it looks like this:

ID Username Password
1 peter admin

I've watched video tutorials and all sorts on this, and even code I have re-used and taken directly word for word from a program that connects to a sql database on a server (but replaced the data source string with that of my own) does not work at all :( still gives me the same problem!

:'(

[megaman21]

Also when I start the program again, the details i previously registered cannot login and have to be re-registered again (i.e the only details that work are those that start off in the table initially).