Database Problem!!!

[Sharon1]

Hello all,

I've been trying to achieve this one thing for ages now, and it's not happening.

My aim is to try to add a new user to a access database. Im doing this using from a form named frmRegister. It seems like the code is working perfectly, and i get no expections that indicate if any errors occured. However, the user doesn't get added to the database and it's causing me extreme headaches trying to figure out why.

The code i'm using is:

Public Class Register
    Dim connectionString As String
    Dim connection As OleDbConnection
    Dim OLEDBAdapter As New OleDbDataAdapter
    Dim OLE As String
    Dim Username As String
    Dim Password As String
    Dim UserType As String

    Private Sub Register_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DataBase\UserInformation.mdb;Persist Security Info=False"
        connection = New OleDbConnection(connectionString)
    End Sub

    Private Sub btnCreateAccount_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateAccount.Click
        If mtbRegisterPassword.Text.Trim = mtbRegisterConfirmPassword.Text.Trim Then
            Register()
        Else
            MsgBox("The password entered do not match.", MsgBoxStyle.Exclamation, "Oops!")
        End If
    End Sub

    Function Register()

        UserType = "Learner"
        Username = txtRegisterUsername.Text.Trim
        Password = mtbRegisterPassword.Text.Trim

        Try
            connection.Open()
            OLE = "INSERT INTO tblUsers (UserName,UserPassword,UserType) VALUES(" + Username + ",'" + Password + "'," + UserType + ")"

            OLEDBAdapter.InsertCommand = New OleDbCommand(OLE, connection)
            MsgBox("Account Created")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        connection.Close()

        Return Nothing
    End Function
End Class

I have attached the project if you wish to look into this further.

(Files to large for attaching here)
Rar format - http://rapidshare.com/files/254374128/Files.rar.html
Zip format - http://rapidshare.com/files/254374401/Files.zip.html

 

[MarkK]

Your code never runs a command. In your Try block you open the connection and assign a command to the InsertCommand property of the Adapter, but then you never do anything with it.
Somewhere you have to ExecuteNonQuery. I forget now if that is a method of the Adapter or the Command, but you see what I'm getting at?

After that, I expect your SQL to fail here...

VALUES(" + Username + ",'" + Password + "',"

...because the field UserName must be a string, so it should be enclosed in single quotes like your Password field. And maybe UserType, maybe not.

Cheers,

 

[Sharon1]

Your code never runs a command. In your Try block you open the connection and assign a command to the InsertCommand property of the Adapter, but then you never do anything with it.
Somewhere you have to ExecuteNonQuery. I forget now if that is a method of the Adapter or the Command, but you see what I'm getting at?

After that, I expect your SQL to fail here...

VALUES(" + Username + ",'" + Password + "',"

...because the field UserName must be a string, so it should be enclosed in single quotes like your Password field. And maybe UserType, maybe not.

Cheers,

Ah I see now.

I'll try and rectify the problems you identified. I'll reply to let you know how it goes.

 

[MarkK]

Yeah, you don't need an OleDbDataAdapter here. You need a OleDbConnection and an OleDbCommand. I'd express your Register function as follows--generally keeping all variables as local as possible...

    Function Register() As Integer
        Dim cnn As OleDbConnection
        Dim cmd As OleDbCommand

        cnn = New OleDbConnection( _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=|DataDirectory|\DataBase\UserInformation.mdb;" & _
            "Persist Security Info=False")

        cmd = New OleDbCommand( _
            "INSERT INTO tblUsers " & _
                "( UserName, UserPassword, UserType ) " & _
            "VALUES " & _
                "( '" & Me.txtRegisterUsername.Text.Trim & "', " & _
                "'" & Me.mtbRegisterPassword.Text.Trim & "', " & _
                "'Learner' )")

        Try
            cnn.Open()
            Return cmd.ExecuteNonQuery
        Catch ex As Exception
            MsgBox(ex.Message)
            Return 0
        Finally
            cnn.Close()
        End Try

    End Function