VB.Net Adding records to MS Access database

[mattwagner]

Hello,

I'm very new to using VB.Net so I found it quite difficult to understand other answers that I searched for.

Basically, I have an project to develop where a user can create an account, and log in with it.

So far I have :

Created a database in ms access with the relevant fields (2003 edition)
Created a VB form with the correct text boxes etc. and linked the text boxes to the database.

What I need :

I would like to be able to code it so that when the form is opened, a new blank record in the database is automatically generated.
When a button is pressed, information in the text boxes will be updated into the new record.

Any help would be appreciated. (I'm very new to VB so the simplest ways would be the best)

Thanks

[debasisdas]

Your question is of very novice level. You need to read books for all those information.

[mattwagner]

I think that I might be close, this is what I've got so far :

Imports System.Data.OleDb


Public Class frmCreateAccount

Dim Mycn As OleDbConnection 
    Dim Command As OleDbCommand
    Dim icount As Integer
    Dim SQLstr As String

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

    Private Sub frmCreateAccount_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DatabaseDataSet.Users' table. You can move, or remove it, as needed.
        Me.UsersTableAdapter.Fill(Me.DatabaseDataSet.Users)
        txtForename.Text = ""
        txtSurname.Text = ""
        txtUsername.Text = ""
        txtPassword.Text = ""
        txtRepPassword.Text = ""
        txtDob.Text = ""
        txtAddress.Text = ""
        txtLicNo.Text = ""
    End Sub



    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

        Try
            Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Matt Wagner\Downloads\Work\New\Login\Database.mdb;")
            Mycn.Open()

            SQLstr = "INSERT INTO Users (Username, Password) VALUES (txtUsername.Text, txtPassword.text)"

            Command = New OleDbCommand(SQLstr, Mycn)
            icount = Command.ExecuteNonQuery
            MessageBox.Show(icount)

        Catch ex As Exception
            MsgBox("ERROR")
            Mycn.Close()
        End Try
    End Sub

    Private Function cmd() As Object
        Throw New NotImplementedException
    End Function

End Class

However I'm getting the message box that says "ERROR" can anyone point out what I'm doing wrong?

Thanks

[KenSquare]

I think you are close too.

SQLstr = "INSERT INTO Users (Username, Password) VALUES (" & txtUsername.Text & ", " & txtPassword.text & ")"

Also, try to get the exception to appear in your message box - not just the word error.

[mattwagner]

I replaced the error box message with

MessageBox.Show(ex.Message & " - " & ex.Source)

Now the error message saysSyntax error in INSERT INTO statement. - Microsoft JET Database Engine

Any ideas

Thanks

[Begginnerdev]

Try

SQLstr = "INSERT INTO Users (Username,Password) VALUES '" & txtUsername.text & "','" & txtPassword.Text & "'"

[mattwagner]

I'm still getting the same Syntax error in INSERT INTO statement.

I've chesked the obvious like the database location and field names etc.

Anywhere else I might be going wrong?

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

        Try
            Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents\Computing\Work\New\Login\Database.mdb;")
            Mycn.Open()

            SQLstr = "INSERT INTO Users (Username,Password) VALUES '" & txtUsername.Text & "','" & txtPassword.Text & "'"

            Command = New OleDbCommand(SQLstr, Mycn)
            icount = Command.ExecuteNonQuery
            MessageBox.Show(icount)

        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
            Mycn.Close()
        End Try
    End Sub

Thanks

[adam_k]

I think it's the missing parrenthesis in values. Change your SQL string to :

SQLstr = "INSERT INTO Users (Username,Password) VALUES ('" & txtUsername.Text & "','" & txtPassword.Text & "')"

[mattwagner]

I've added the parrenthesis and i'm still getting the same error, However i noticed that in the Immediate Window that it says "A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll"

Any More Ideas?

Thanks

[Begginnerdev]

Wrap the code in a try/catch block. Then use msgbox(ex.message) in the catch.

[mattwagner]

Wrap the code in a try/catch block. Then use msgbox(ex.message) in the catch.

Haven't I already done that? If not how do I?

Thanks

[Begginnerdev]

Sorry, my fault. I had forgotten you did so.

Have you checked to be sure you have permissions to access the database?

[mattwagner]

Yes, the database isn't password protected and I can view the current content of a database through a datagrid, so I dont think that permisions are the problem?

The errors are showing as the SQLString, I've made sure that the table names are correct and other obvious things, i'm really at a dead end.

Any help would be appriciated.
Thanks

[Begginnerdev]

Just for the sake of redundency, have you tried:

SQLstr = "INSERT INTO users (username,password) VALUES ('" & txtUserName.text & "','" & txtPassword.text & "'"

Maybe it has something to do with case sensitivity?

If not, have you checked to make sure that the names are correct? (I know it's stupid, but it helps eliminate problems")

[adam_k]

I'm not sure about this, but could it be that either users,username or password is reserved word for Access and that is giving you errors? try brackets around this words and see if it helps.

[Ggalla1779]

You also have to be careful if there is no value entered VB form side then you dont update this field

What does that mean..well as you create your insert SQL you must craft this by hand...ie if there is value in Forename then ok I need to add that to Insert.... and build up sql

ok your new so switch to access and goto table view open the table, now add a record...understand what it takes to add a record ie do all fields have to be entered? what minimium values must have...also note if you have autonumber it adds itself in...

You should also be able to find complete samples of what your doing on the web...if you cant message me I will point you to right place

[mattwagner]

Okay, I completley started again from scratch but i'm still having the same problems

Syntax Error in INSERT INTO STATEMENT

I rewrote my code so that it is different to before.

Public Class frmCreateAccount

    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim inc As Integer
    Dim MaxRows As Integer

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

    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

        txtUsername.Clear()
        txtPassword.Clear()
        txtForename.Clear()
        txtSurname.Clear()
        txtRepPassword.Clear()
        txtDob.Clear()
        txtAddress.Clear()
        txtLicNo.Clear()

        'If inc <> -1 Then
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("Database").NewRow()
        dsNewRow.Item("Username") = txtUsername.Text
        dsNewRow.Item("Password") = txtPassword.Text
        dsNewRow.Item("First Name") = txtForename.Text
        dsNewRow.Item("Second Name") = txtSurname.Text
        dsNewRow.Item("DOB") = txtDob.Text
        dsNewRow.Item("Address") = txtAddress.Text
        dsNewRow.Item("Licence Number") = txtLicNo.Text
        ds.Tables("Database").Rows.Add(dsNewRow)
        da.Update(ds, "Database")
        MsgBox("User Created")
        'End If

    End Sub

    Private Sub frmCreateAccount_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = H:\MyWork\LOGIN SYSTEM\New\Login\Database.mdb"
        con.ConnectionString = dbProvider & dbSource
        con.Open()

        sql = "SELECT * FROM Users"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Database")
        MaxRows = ds.Tables("Database").Rows.Count
        inc = -1

        con.Close()

    End Sub
End Class

But VB is showing an error in the lineda.Update(ds, "Database")

Is there something I might have not put in?

Any help appriciated

Thanks

[Begginnerdev]

I see that you are clearing your text boxes then asigning them to the ds. Are any of your fields set to be not null? If so, then you may be having a problem there.

[mattwagner]

I just noticd that the textboxes were being cleared, so I corrected that, But i'm still having the same problem...

[Begginnerdev]

I use a datatable method, which is close to what you are doing.

Here is what I do:

'Where table name is the table in the db.
 Dim dt As DataTable = ds.Tables("tablename")

 Dim newRow As DataRow
'Setting up the table for the data
 newRow = dt.NewRow()
'You should replace "column1" and "column2" with the column name in your db.
 newRow("column1") = TextBox1.Text
 newRow("column2") = TextBox2.Text
 dt.Rows.Add(newRow)

'Updating db
da.Update(ds, "tablename")

See if this helps.