VB.Net Adding records to MS Access database

Question

mattwagner 0 Newbie Poster

12 Years Ago

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

vb.net

9 Contributors
33 Replies
7K Views
10 Months Discussion Span
Latest Post 11 Years Ago Latest Post by adam_k

Begginnerdev 256 Junior Poster

12 Years Ago

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.

Begginnerdev 256 Junior Poster

12 Years Ago

Connection will be the name of the your connection.

For you connection would be "con"

so

da.UpdateCommand = New OleDBCommand(sqlString, con)

You will be able to place the code anywhere after the declaration of the data adapter and string, but before the da.Update is called.

Edited 12 Years Ago by Begginnerdev because: n/a

Phasma 11 Junior Poster in Training

12 Years Ago

mattwagner, could you go to your MS Access table that you are trying to update/Insert to and copy/paste the Column names here? I am curious from reading your thread and the code samples, that you have column names with spaces in which case you will get the errors you are receiving. If your sql's column names are not in [Column One] format it will not work.

Ken

Phasma 11 Junior Poster in Training

12 Years Ago

First I would us a Global DataTable/DataSet, OleDbDataAdapter and BindingSource. Fill your table and bind it to your DataGridView through the BindingSource. When you fill your DataSet Add the CommandBuilder to the OleDbDataAdapter. Add or Change current values in your datagridview/DataTable as you see fit then call the OleDbDataAdapter Update method.

Here is a simple Example. It reads all the records from the .mdb table displays them in a datagridview, which is bound to a the filled DataTable by a BindingSource. When Ever a change is made to it or a row is added it will be reflected back to the .mdb table. I added a Sub that adds a row by code. An Image of the .mdb table is attached

Let me know if you need any more help

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
  Dim wrkDir As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location())
  Dim da As New OleDbDataAdapter
  Dim ds As New DataSet
  Dim bs As New BindingSource
  Dim edit As Boolean
  'Coupon_Tracker.mdb
  Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                  "Data Source=" & wrkDir & "\Coupon_Tracker.mdb")


  ''' <summary>
  ''' Update The DataBase
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    If edit Then
      da.Update(ds, "EXTENSIONS")
      edit = False
    End If

  End Sub

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    'Set the dataGridView's DataSource
    dgv1.DataSource = bs
  End Sub

  ''' <summary>
  ''' Adds a row by code
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub tsBtnLoad_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) Handles tsBtnLoad.Click

    ds.Tables("EXTENSIONS").Rows.Add(".TEST", "Dani_Web_Example", "NOTHING OPENS THIS FILE")


  End Sub

  Private Sub dgv1_CellEndEdit(ByVal sender As Object, _
                               ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
                               Handles dgv1.CellEndEdit
    edit = True
  End Sub

  ''' <summary>
  ''' Loads all the records to the DataGridView
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub btnLoadAll_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) Handles btnLoadAll.Click
    ds.Tables.Clear()

    Dim sql As String = "SELECT * FROM(Extensions);"

    Dim cmd As New OleDbCommand(sql, conn)

    da.SelectCommand = cmd
    Dim cmdBuilder As New OleDbCommandBuilder(da)
    da.Fill(ds, "EXTENSIONS")
    bs.DataSource = ds.Tables(0)

  End Sub

  ''' <summary>
  ''' Sets edit to true when user deletes a row or rows
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="e"></param>
  ''' <remarks></remarks>
  Private Sub dgv1_RowsRemoved(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowsRemovedEventArgs) Handles dgv1.RowsRemoved
    edit = True
  End Sub
End Class

Edited 12 Years Ago by Phasma because: Added Images

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

debasisdas 580 Posting Genius Featured Poster · Answer 1 · 2012-01-17T00:12:30+00:00

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

mattwagner 0 Newbie Poster · Answer 2 · 2012-01-17T01:09:01+00:00

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 0 Newbie Poster · Answer 3 · 2012-01-17T06:15:02+00:00

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 0 Newbie Poster · Answer 4 · 2012-01-17T20:23:34+00:00

I replaced the error box message with

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

Now the error message says

Syntax error in INSERT INTO statement. - Microsoft JET Database Engine

Any ideas

Thanks

Begginnerdev 256 Junior Poster · Answer 5 · 2012-01-17T20:28:32+00:00

Try

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

mattwagner 0 Newbie Poster · Answer 6 · 2012-01-17T20:50:19+00:00

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 239 Master Poster · Answer 7 · 2012-01-18T03:18:58+00:00

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 0 Newbie Poster · Answer 8 · 2012-01-24T19:53:39+00:00

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 256 Junior Poster · Answer 9 · 2012-01-24T19:56:08+00:00

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

mattwagner 0 Newbie Poster · Answer 10 · 2012-01-24T20:00:07+00:00

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 256 Junior Poster · Answer 11 · 2012-01-25T01:20:01+00:00

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

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

mattwagner 0 Newbie Poster · Answer 12 · 2012-01-25T18:53:38+00:00

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 256 Junior Poster · Answer 13 · 2012-01-26T00:48:35+00:00

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 239 Master Poster · Answer 14 · 2012-01-27T00:39:43+00:00

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 0 Newbie Poster · Answer 15 · 2012-01-27T17:38:18+00:00

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 0 Newbie Poster · Answer 16 · 2012-01-27T19:34:14+00:00

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 line

da.Update(ds, "Database")

Is there something I might have not put in?

Any help appriciated

Thanks

mattwagner 0 Newbie Poster · Answer 17 · 2012-01-27T19:51:24+00:00

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

Begginnerdev 256 Junior Poster · Answer 18 · 2012-01-27T19:53:50+00:00

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.

Begginnerdev 256 Junior Poster · Answer 19 · 2012-01-27T19:58:40+00:00

I have also had issues in the past when using the wildcard, see if you can directly reference the names to the columns. I have had the wildcard jumble everything together with no order. It seems to do it mostly when there are empty columns in the tables, so it pushes the rest of the data together.

mattwagner 0 Newbie Poster · Answer 20 · 2012-01-27T20:07:00+00:00

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.

I tried that and didn't get the usual error but instead got.

NullReferenceException was unhandled
Object reference not set to an instance of an object.

Am I closer?

EDIT: In the line :

"newRow = dt.NewRow()"

Begginnerdev 256 Junior Poster · Answer 21 · 2012-01-27T20:13:03+00:00

Sorry forgot to add:

Dim newRow As DataRow

My appologies friend.

mattwagner 0 Newbie Poster · Answer 22 · 2012-01-27T20:18:41+00:00

Sorry forgot to add:
Dim newRow As DataRow
My appologies friend.

I've added that now and have even another error. This time it is:

InvalidOperationException was unhandled
Update requires a valid InsertCommand when passed DataRow collection with new rows.

which shows up in the line

da.Update(ds, "Users")

Any more help is greatly appriciated.
Thank you for helping me so far :)

Begginnerdev 256 Junior Poster · Answer 23 · 2012-01-27T20:28:47+00:00

dim sqlString As String = "INSERT INTO Users (Username,Password,First Name,Second Name,DOB,Address,License Number) VALUES ('" & txtUsername.text & "','" & txtPassword.text & "','" & txtForename.Text & "','" txtSurname.Text & "','" & txtDob.text & "','" & txtAddress.Text & "','" & txtLicNo.Text & "')"


da.UpdateCommand = New OleDbCommand(sqlString, connection)

You might want to check the sql string for errors, just typed it into the text widow.

mattwagner 0 Newbie Poster · Answer 24 · 2012-01-27T20:39:44+00:00

Okay, where abouts should it go in the code, and

da.UpdateCommand = New OleDbCommand(sqlString, connection)

It says connection is not declared, should I just declare it as a string?

mattwagner 0 Newbie Poster · Answer 25 · 2012-02-03T18:41:45+00:00

Sorry for the late reply.

My Coulumn names had spaces but i have corrected them and my code so that the collum names are:

ID / Username / Password / FirstName / SecondName / DOB / Address / LicenceNumber

and my code is

Imports System.Data.OleDb

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 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, "Users")
        MaxRows = ds.Tables("Users").Rows.Count
        inc = -1

        con.Close()

    End Sub

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


        Dim dt As DataTable = ds.Tables("Users")

        Dim newRow As DataRow

        newRow = dt.NewRow()

        newRow("ID") = txtid.Text
        newRow("Username") = txtUsername.Text
        newRow("Password") = txtPassword.Text
        newRow("FirstName") = txtForename.Text
        newRow("SecondName") = txtSurname.Text
        newRow("DOB") = txtDob.Text
        newRow("Address") = txtAddress.Text
        newRow("LicenceNumber") = txtLicNo.Text
        dt.Rows.Add(newRow)

        Dim sqlString As String = "INSERT INTO Users (Username,Password,FirstName,SecondName,DOB,Address,LicenseNumber) VALUES ('" & txtUsername.Text & "','" & txtPassword.Text & "','" & txtForename.Text & "','" & txtSurname.Text & "','" & txtDob.Text & "','" & txtAddress.Text & "','" & txtLicNo.Text & "')"
        da.UpdateCommand = New OleDbCommand(sqlString, con)


        da.Update(ds, "Users")
    End Sub
End Class

And the error i'm getting at the moment is

InvalidOperationException was unhandled
Update requires a valid InsertCommand when passed DataRow collection with new rows.

Any help appriciated.