Adding and Saving Records to Access Database using VB.NET Tutorial

Submitted by donbermoy on Wednesday, December 2, 2020 - 10:08.
This is a continuation of my other tutorials entitled Search and Retrieve a Record from Access Database and VB.NET. But here, we will focus on adding and saving a record into an access database. Now, let's start this tutorial! 1. Create an access file with the table and entities like the image below. Name your access database as sample.mdb(2003 format). dabase 2. Create a Windows Form Application in VB.NET for this tutorial by following the following steps in Microsoft Visual Studio: Go to File, click New Project, and choose Windows Application. 3. Next, add another Button for adding records (Assume that we are done with the search button here as you will open the link I have write above.) named btnAdd and labeled it as "Add". This will add and save the inputted texts in all our textboxes. Insert 4 textbox named txtSearch for the searching of student number as this is the primary key, txtName for Student Name, txtCourse for Student course, and txtSection for student section. You must design your interface like this: design 4. Create a module in your project and named it, modConnection. Import Imports System.Data.OleDb library. This library package is for ms access database. In your module connection, initialize the following variables. 
  1.     Module modConnection
  2.  
  3.     Public cn As New OleDb.OleDbConnection
  4.     Public cm As New OleDb.OleDbCommand
  5.     Public dr As OleDbDataReader
Now, create a method named connection to have the connection string. This will locate the sample.mdb access database that we have created earlier. 
  1.     Public Sub connection()
  2.     cn = New OleDb.OleDbConnection
  3.     With cn
  4.     'Provider must be Microsoft.Jet.OLEDB.4.0, find the access file, and test the connection
  5.     .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\sample.mdb"
  6.     .Open()
  7.     End With
  8.     End Sub
Full code of the modConnection module. 
  1.     Imports System.Data.OleDb
  2.     Module modConnection
  3.  
  4.     Public cn As New OleDb.OleDbConnection
  5.     Public cm As New OleDb.OleDbCommand
  6.     Public dr As OleDbDataReader
  7.  
  8.     Public Sub connection()
  9.     cn = New OleDb.OleDbConnection
  10.     With cn
  11.     .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\sample.mdb"
  12.     .Open()
  13.     End With
  14.     End Sub
  15.     End Module
5. Back to our form, put this code for the form_load. We will call the connetion() method that we have created in our module. Because we all know that module is access throughout the entire program. 
  1.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.     Call connection()
  3.     End Sub
6. For the btnAdd as adding and saving records to database button, put this code below. We will first create a try and catch method to have the exception handling. Here, we will use the OleDbCommand with the method of connection, CommandType.Text, and CommandText, with the OleDbDataReader to execute the CommandText. Note: The sql syntax "INSERT INTO tblStudent (Snum,Sname,Scourse,Ssection) VALUES (@Snum,@Sname,@Scourse,@Ssection)" is for adding and saving of records to the database. Snum,Sname,Scourse, and Ssection are our entities and @Snum,@Sname,@Scourse,@Ssection are our parameters. 
  1.   Try
  2.  
  3.             cm = New OleDb.OleDbCommand
  4.             With cm
  5.                 .Connection = cn
  6.                 .CommandType = CommandType.Text
  7.                 .CommandText = "INSERT INTO tblStudent (Snum,Sname,Scourse,Ssection) VALUES (@Snum,@Sname,@Scourse,@Ssection)"
Now, have your parameters hold the value inputted in your textboxes that you have created earlier in the interface. 
  1.    .Parameters.Add(New System.Data.OleDb.OleDbParameter("@Snum", System.Data.OleDb.OleDbType.VarChar, 255, Me.txtSearch.Text))
  2.                 .Parameters.Add(New System.Data.OleDb.OleDbParameter("@Sname", System.Data.OleDb.OleDbType.VarChar, 255, Me.txtName.Text))
  3.                 .Parameters.Add(New System.Data.OleDb.OleDbParameter("@Scourse", System.Data.OleDb.OleDbType.VarChar, 255, Me.txtCourse.Text))
  4.                 .Parameters.Add(New System.Data.OleDb.OleDbParameter("@Ssection", System.Data.OleDb.OleDbType.VarChar, 255, Me.txtSection.Text))
To run the command of our parameters put this code below. 
  1.  cm.Parameters("@Snum").Value = Me.txtSearch.Text
  2.                 cm.Parameters("@Sname").Value = Me.txtName.Text
  3.                 cm.Parameters("@Scourse").Value = Me.txtCourse.Text
  4.                 cm.Parameters("@Ssection").Value = Me.txtSection.Text
Execute the non query and have a message of "Record Saved" after execution. 
  1.  cm.ExecuteNonQuery()
  2. MsgBox("Record saved.", MsgBoxStyle.Information)
Clear all the textboxes after saving the records. And don't forget the Exit Sub command at the end of your Try method. This will terminate the sub process of trying to add again another record. 
  1.  Me.txtCourse.Text = ""
  2.  Me.txtName.Text = ""
  3.  Me.txtSearch.Text = ""
  4.  Me.txtSection.Text = ""
  5.  Exit Sub
Then to catch the error, put this in your catch method. 
  1. Catch ex As Exception
  2.             MsgBox(ex.Message, MsgBoxStyle.Critical)
  3.         End Try
Here is the full source code of our tutorial for adding and saving records with search also. 
  1. Public Class Form1
  2.  
  3.     Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
  4.         Dim found As Boolean
  5.         Try
  6.  
  7.             cm = New OleDb.OleDbCommand
  8.             With cm
  9.                 .Connection = cn
  10.                 .CommandType = CommandType.Text
  11.                 .CommandText = "SELECT * FROM tblStudent WHERE (Snum = '" & txtSearch.Text & "')"
  12.                 dr = .ExecuteReader
  13.             End With
  14.             While dr.Read()
  15.  
  16.                 txtName.Text = dr("Sname").ToString
  17.                 txtCourse.Text = dr("Scourse").ToString
  18.                 txtSection.Text = dr("Ssection").ToString
  19.                 found = True
  20.  
  21.             End While
  22.             cn.Close()
  23.             Exit Sub
  24.             If found = False Then MsgBox("Student ID not found.", MsgBoxStyle.Critical)
  25.             dr.Close()
  26.         Catch ex As Exception
  27.  
  28.         End Try
  29.     End Sub
  30.  
  31.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  32.         Call connection()
  33.     End Sub
  34.  
  35.     Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
  36.         Try
  37.  
  38.             cm = New OleDb.OleDbCommand
  39.             With cm
  40.                 .Connection = cn
  41.                 .CommandType = CommandType.Text
  42.                 .CommandText = "INSERT INTO tblStudent (Snum,Sname,Scourse,Ssection) VALUES (@Snum,@Sname,@Scourse,@Ssection)"
  43.  
  44.                 .Parameters.Add(New System.Data.OleDb.OleDbParameter("@Snum", System.Data.OleDb.OleDbType.VarChar, 255, Me.txtSearch.Text))
  45.                 .Parameters.Add(New System.Data.OleDb.OleDbParameter("@Sname", System.Data.OleDb.OleDbType.VarChar, 255, Me.txtName.Text))
  46.                 .Parameters.Add(New System.Data.OleDb.OleDbParameter("@Scourse", System.Data.OleDb.OleDbType.VarChar, 255, Me.txtCourse.Text))
  47.                 .Parameters.Add(New System.Data.OleDb.OleDbParameter("@Ssection", System.Data.OleDb.OleDbType.VarChar, 255, Me.txtSection.Text))
  48.  
  49.  
  50.                 ' RUN THE COMMAND
  51.                 cm.Parameters("@Snum").Value = Me.txtSearch.Text
  52.                 cm.Parameters("@Sname").Value = Me.txtName.Text
  53.                 cm.Parameters("@Scourse").Value = Me.txtCourse.Text
  54.                 cm.Parameters("@Ssection").Value = Me.txtSection.Text
  55.  
  56.  
  57.                 cm.ExecuteNonQuery()
  58.                 MsgBox("Record saved.", MsgBoxStyle.Information)
  59.                 Me.txtCourse.Text = ""
  60.                 Me.txtName.Text = ""
  61.                 Me.txtSearch.Text = ""
  62.                 Me.txtSection.Text = ""
  63.                 Exit Sub
  64.             End With
  65.         Catch ex As Exception
  66.             MsgBox(ex.Message, MsgBoxStyle.Critical)
  67.         End Try
  68.     End Sub
  69. End Class
Output: outputoutput The database is located inside the Debug folder of Bin. Best Regards,

Engr. Lyndon R. Bermoy
IT Instructor/System Developer/Android Developer
Mobile: 09079373999
Telephone: 826-9296
E-mail:serbermz2020@gmail.com

Visit and like my page on Facebook at: Bermz ISware Solutions

Subscribe at my YouTube Channel at: SerBermz

Tags

Comments

Submitted byvalpuia (not verified)on Thu, 07/23/2015 - 13:37

Best site ever... llove it tytyty
Submitted byMujeeb (not verified)on Wed, 11/23/2016 - 14:12

i trying to add my text box data to ms access database using vb2010 , i connected my database with vb using connection wizard . but when i add data to databse first set of data is corectily savied in database but net time i try to add new data to database it showing error pls help me to corect the same here iam attaching the code and the error msg Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'Masterdata_mdbDataSet.Machine' table. You can move, or remove it, as needed. Me.MachineTableAdapter.Fill(Me.Masterdata_mdbDataSet.Machine) End Sub Private Sub Add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Add.Click MachineBindingSource.AddNew() End Sub Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click MachineBindingSource.EndEdit() MachineTableAdapter.Update(Masterdata_mdbDataSet.Machine) End Sub Msg is Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Submitted bySoukhin Basu (not verified)on Sat, 11/26/2016 - 20:05

I wrote this code but ut show an error message ("ExecuteNonQuery requires an open and available connection.The connection's current state is closed")

Submitted byEsco (not verified)on Tue, 07/18/2017 - 17:21

everything went well expect if i search for the student and then want to search for another student it wouldnt change the info. also i have another question how can i share the database in local network? for example i have 5 pcs ill install the program on all of them but i want them to save in the same database please advise

copy Call connection() on form load and past it on the button that execute the search records. the problem is that the collection has closed after executing the first command.
Submitted byTHEONE (not verified)on Sun, 07/23/2017 - 18:59

I wrote this code but it show an error message ("ExecuteNonQuery requires an open and available connection.The connection's current state is closed")
Submitted byRandom Asker (not verified)on Thu, 03/08/2018 - 21:31

Why is this error" Microsoft.Jet.OLEDB.4.0" keep showing even though i follow every steps? Can you fix it?
Submitted bybj molo (not verified)on Sat, 06/23/2018 - 09:11

the database don't save permanently
Submitted byMdm mdm (not verified)on Sun, 08/26/2018 - 03:16

Cause the database your updating is on debug, it reset the database whenever you finish you running your code so user will be easily to experiment on the database. If you want to permanent save it, use the database file outside debug. but its a hassle if you want to delete some data every time you try to run the program
Submitted byMdm mdm (not verified)on Sun, 08/26/2018 - 03:12

Hi, can you also give us a code for updating database using also parameterized query? Its just doesn' work for me, I mean, its working but the database doesn't update at all
Submitted byIts very incomplete (not verified)on Wed, 08/04/2021 - 16:30

Its nice that it uses parameter in adding Data to a Database but you need to fix the connection error
Submitted byfuturenewsforyou.com (not verified)on Sat, 07/02/2022 - 20:15

Find out to determine Client Life time Worth utilizing DCF & & innovative designs & & utilize it to boost the business’s productivity What you will certainly discover Find out to determine client’s life time worth under various circumstances and also utilize it to boost the business’s productivity. Integrate the effect of price cut price and also retention price to determine client worth Run Monte-Carlo Simulation to design uncertainity in Market This is really an interesting blog.Keep sharing i really appreciate the work
Submitted byHIM (not verified)on Mon, 04/10/2023 - 15:38

THOSE WHO HAVE PROBLEM IN Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine, JUST CHANGE 4.0 TO 12.0

Add new comment

About text formats