[kasmar]

I have made this user account creation screen for a login application I am making. I have made the account creator before the actual login part because I wanted some accounts in there. I need help writing the rest because I am unsure if I have to make a query in the application or a loop that goes through all the records.

Basicaly I need help to make the code to check if the username is in the database already and then return an error screen if it is or just continue the code and write it.

Imports System.Data

Public Class Form2

	Dim inc As Integer
	Dim MaxRows As Integer

	Dim con As New OleDb.OleDbConnection
	Dim ds As New DataSet
	Dim da As OleDb.OleDbDataAdapter
	Dim sql As String

	Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Accounts.mdb;Jet Oledb:Database Password=kas;"
		con.Open()

		sql = "SELECT * FROM tblAccounts"
		da = New OleDb.OleDbDataAdapter(sql, con)

		da.Fill(ds, "Accounts")

		con.Close()

		MaxRows = ds.Tables("Accounts").Rows.Count
		inc = 0
	End Sub

	Private Sub NavigateRecords()

		TextBox1.Text = ds.Tables("Accounts").Rows(inc).Item(1)
		TextBox2.Text = ds.Tables("Accounts").Rows(inc).Item(2)

	End Sub

	Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
		If TextBox1.Text = "" Then
			'Show an Error Message.
			MsgBox("Error, You need at least 1 Letter on your Username.")
		ElseIf inc <> -1 Then

			Dim cb As New OleDb.OleDbCommandBuilder(da)
			Dim dsNewRow As DataRow

			dsNewRow = ds.Tables("Accounts").NewRow()

			dsNewRow.Item(1) = TextBox1.Text
			dsNewRow.Item(2) = TextBox2.Text

			ds.Tables("Accounts").Rows.Add(dsNewRow)

			da.Update(ds, "Accounts")
			TextBox1.Clear()
			TextBox2.Clear()

			MsgBox("Your account has been created.")
			Form1.Show()
			Me.Close()
		End If
	End Sub

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

	Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
		TextBox2.PasswordChar = ("*")
	End Sub
End Class

I created all of this off of a few tutorials that I found of some sites but they did not go into the database much.

[kasmar]

Ok so I worked on the code some more and came up with a loot that checks for the duplicate account but the problem is that it will only go one recorde at a time and I have to click the button to advance it. I added the following code to the create button.

		ElseIf inc <> MaxRows - 1 AndAlso ds.Tables("Accounts").Rows(inc).Item(1) <> TextBox1.Text Then
			inc = inc + 1

		ElseIf ds.Tables("Accounts").Rows(inc).Item(1) = TextBox1.Text Then
			inc = 0
			TextBox1.Clear()
			TextBox2.Clear()
			MsgBox("User account exists.")

I said above that I am not good with loops, can someone help me get this to loop through all the records without having to click each time?

[PsychoCoder]

What I would do would be create a simple function (like the one below) that queries your database to see if an account already exists with that username. Have it return a Boolean value (True or False) based on the results of the query. Something like this

Private Function DoesAccountExist(ByRef username As String, ByRef pwd As String)
	con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Accounts.mdb;Jet Oledb:Database Password=kas;"
    con.Open()

    sql = "Select COUNT(*) FROM tblAccounts WHERE Username = '" & username & "' AND password='" & password & "'"
    Dim cmd As New OleDb.OleDbCommand(sql,conn)
    Dim rows As Integer = cmd.ExecuteScalar()
    
    If rows > 0 Then
    	Return True
    Else
    	Return False
    End If
End Function

Then you can call it in the button click event like so

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
    If TextBox1.Text = "" Then
        'Show an Error Message.
        MsgBox("Error, You need at least 1 Letter on your Username.")
    ElseIf inc <> -1 Then
		If Not DoesAccountExist(TextBox1.Text,TextBox2.Text) Then
			Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("Accounts").NewRow()

            dsNewRow.Item(1) = TextBox1.Text
            dsNewRow.Item(2) = TextBox2.Text

            ds.Tables("Accounts").Rows.Add(dsNewRow)

            da.Update(ds, "Accounts")
            TextBox1.Clear()
            TextBox2.Clear()

            MsgBox("Your account has been created.")
            Form1.Show()
            Me.Close()
		Else
			MessageBox.Show("An account already exists with that username. Please try a different username")
		End If            
    End If
End Sub

That should at least gt you going down the right path

[kasmar]

That works great, I set it up to work in my app with my variables, but if someone fails to make an account twice it errors saying "Not allowed to change the 'ConnectionString' property. The connection's current state is open."
So I put a con.close in there and that worked out. I also set up, if they had not password, it would error too.

Thanks for your help PsychoCoder, would you mind if I site you in my documentation when I finish making this login?

I am going to use a modified version of the code to do the authentication, this was just the creation. So I may be back tonight if I get stuck haha.

Here is the code for creating user accounts and searching for duplicates in an Access database:

Imports System.Data

Public Class Form2

    Dim inc As Integer
    Dim MaxRows As Integer

    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Accounts.mdb;Jet Oledb:Database Password=kas;"
        con.Open()

        sql = "SELECT * FROM tblAccounts"
        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "Accounts")

        con.Close()

        MaxRows = ds.Tables("Accounts").Rows.Count
        inc = 0
    End Sub

    Private Function DoesAccountExist(ByRef username As String)
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Accounts.mdb;Jet Oledb:Database Password=kas;"
        con.Open()

        sql = "Select COUNT(*) FROM tblAccounts WHERE FirstName = '" & username & "'"
        Dim cmd As New OleDb.OleDbCommand(sql, con)
        Dim rows As Integer = cmd.ExecuteScalar()

        con.Close()

        If rows > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        If TextBox1.Text = "" Then
            'Show an Error Message.
            MsgBox("Error, You need at least 1 Letter on your Username.")
        ElseIf TextBox2.Text = "" Then
            'Show an Error Message.
            MsgBox("Error, You need at least 1 Letter on your Password.")
        ElseIf inc <> -1 Then
            If Not DoesAccountExist(TextBox1.Text) Then

                Dim cb As New OleDb.OleDbCommandBuilder(da)
                Dim dsNewRow As DataRow

                dsNewRow = ds.Tables("Accounts").NewRow()

                dsNewRow.Item(1) = TextBox1.Text
                dsNewRow.Item(2) = TextBox2.Text

                ds.Tables("Accounts").Rows.Add(dsNewRow)

                da.Update(ds, "Accounts")
                TextBox1.Clear()
                TextBox2.Clear()

                MsgBox("Your account has been created.")
                Form1.Show()
                Me.Close()
            Else
                MessageBox.Show("An account already exists with that username. Please try a different username")
                TextBox1.Clear()
                TextBox2.Clear()
            End If
        End If
    End Sub

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

    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
        TextBox2.PasswordChar = ("*")
    End Sub
End Class

[PsychoCoder]

No problem kazmar, glad it worked out for you

You can site me if you like, but it's not a requirement for getting help with a problem