1. Visual Basic .NET
  2. How to Add/Update/Delete Record using MS Access Database

How to Add/Update/Delete Record using MS Access Database

Submitted by admin on Tuesday, March 10, 2009 - 15:01.
Language
Visual Basic .NET

The primary purpose of this code is to teach beginner programmer to familiarize the concept of database programming.

This is particularly for beginner but may also applicable for intermediate programmer.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Tags
ms access
vb.net
database

Comments

Submitted by Anonymous (not verified) on Tue, 10/20/2009 - 02:53

thank you>>>>>>>>>>>>>>>

Submitted by Anonymous (not verified) on Thu, 11/12/2009 - 18:42

how we can use " insert command " in vb ? pls send me some examples which describe the code

Submitted by Anonymous (not verified) on Thu, 12/10/2009 - 14:26

Could you please help me i used all types of memory stream code i have an error parameter in not valid; here is my code
  1. OleDbCommand cmd = new OleDbCommand("SELECT photo FROM stdetails where stuid='" + txtstid.Text + "'", conn);
  2.  
  3.             DataSet ds = new DataSet();
  4.             OleDbDataAdapter da = new OleDbDataAdapter(cmd);
  5.             da.Fill(ds, "stdetails");
  6.             byte[] content = (byte[])ds.Tables[0].Rows[0]["photo"];
  7.  
  8.             try
  9.             {
  10.  
  11.                 MemoryStream stream = new MemoryStream(content,true);
  12.  
  13. // i have problem in this place
  14.              pictureBox2.Image = Image.FromStream(stream,true,true);
  15.  
  16.             }
  17.  
  18.             catch (Exception ex)
  19.             {
  20.  
  21.                 MessageBox.Show(ex.Message.ToString());
  22.  
  23.                 MessageBox.Show(ex.StackTrace.ToString());
  24.  
  25.             }

Submitted by Anonymous (not verified) on Mon, 12/28/2009 - 09:53

Hi, I'm beginner of VB.NET. Pls help me about the database connection using vb.net with sql server,ms access and oracle with step by step process with screen shot. pls pls pls........... My mail ID is:[email protected]

Submitted by Anonymous (not verified) on Tue, 01/26/2010 - 10:53

..plz post the step by step on how to add/update/and delete the record using ms access..tnx...i will wait for the response...Godbless....!!!

Submitted by Anonymous (not verified) on Fri, 03/12/2010 - 10:58

i face a problem saving password to Access. it work nicely if i remove password. the error occur on INSERT and UPDATE query. please help. using OleDb.. i can c the password value from the sqlcommand, but it just pop up the error message.

Submitted by Anonymous (not verified) on Sat, 03/20/2010 - 17:55

i hpe yor code will help me in mine...:)

Submitted by Anonymous (not verified) on Wed, 04/21/2010 - 01:03

cant see no codes here although i clicked to download them

Submitted by Anonymous (not verified) on Sun, 04/25/2010 - 21:51

Hi, I am using msaccess 2003 as backend in project. The code is just saving data which newly added but I want to update existing record of database using bindingnavigator. The code is throwing exception. Please help me as soon as possible since the date of my project submission is coming nearer. Thanks [email protected]

Submitted by Anonymous (not verified) on Mon, 04/26/2010 - 23:24

when ever i update my record it updtae only one record. if i add no-1 and then no-2. so in my view report only 2 will show 1 will delete , why its is happening

Submitted by Anonymous (not verified) on Sun, 06/20/2010 - 15:32

Hi, I am a beginner (vb.net programmer). I have ms access database with 2 tables. I connect to the database successfully but I want the primary key (Auto number) in table two to automatically appear in Foreign Key (Number) in table one after setting up the relationship. Please I need your help. [email protected] Thanks in advance.

Submitted by Anonymous (not verified) on Thu, 07/29/2010 - 12:29

hi, I am a start with vb.net. i have one table in Ms Access 2003 location is drive c: but I am unknown about insert data from vb.net to Ms Access 2003. Please, Can you tell me about this code? [email protected] Thank you!

Submitted by Anonymous (not verified) on Thu, 09/09/2010 - 21:57

Write in Form:- Imports System.Data.OleDb Public Class form1 Dim v1 As New Class1(Application.StartupPath) Dim ds As New DataSet Private Sub btninsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btninsert.Click v1.ExecCommand("insert into tablename values("Textbox1.Text"','" + Textbox2.Text + "')") MsgBox("Insert Successfully") End Sub Write in Class:- Imports System.Data.OleDb Public Class Class1 Dim cn As OleDbConnection Dim cm As OleDbCommand Dim da As OleDbDataAdapter Dim ds As DataSet Public vfrm As New FrmRecord Public Sub New(ByVal path As String) cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\Database.mdb(Table name which is in ms-access") End Sub Public Function GetDataSet(ByVal qry As String) As DataSet cm = New OleDbCommand(qry, cn) da = New OleDbDataAdapter(cm) ds = New DataSet da.Fill(ds) Return ds End Function

Submitted by Anonymous (not verified) on Fri, 09/24/2010 - 20:21

Try this code out, hope it helps: Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\dercio.mnisi\Desktop\Samito.mdb" Dim OleDBConne1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(connString) OleDBConne1.Open()` Dim SQLString As String = "INSERT INTO Customer(customer_First_Names,customer_Surname,customer_Cell)VALUES('" & firstNames & "','" & surnames & "','" & cellNum & "')"

Submitted by Anonymous (not verified) on Mon, 01/31/2011 - 01:25

go to Data then "addnewdatasource" just click Next the default is database then then after you click Next click on new connection and click Change and choose this "Microsoft Access Database File (OLE DB)" then test connection, if its successful your doing right then click OK note: if theres a messagebox appear during this just click No. as i say No if you dont want to get error.. click Next now after next choose your database objects... and click Finish.. go to Data again and click show datasource.. u can click and drag the data to forms... just check to properties to datasources... hope it helps...

Submitted by Anonymous (not verified) on Sun, 06/27/2010 - 10:56

i don know the code insert n delete in database M.Eccess 2007 using button combo box..and arrage the list of combobox follow by alphabet...can 2 help me

Submitted by Anonymous (not verified) on Mon, 08/02/2010 - 22:55

pls sir i really need your help, i need a code that can save data and image from vb.net 2010 to Microsoft access 2003....can u mail it to my box at [email protected]

Submitted by Anonymous (not verified) on Mon, 08/16/2010 - 11:08

i have implement my system using Ms acceess 2003. Now i have a problem with button. I have created one button that name SAVE button..i want this button have two function wich is it can ADD/DELETE at two different table. Can u help me?? Plese give a response at my email [email protected]

Submitted by Anonymous (not verified) on Thu, 09/16/2010 - 13:02

When i open there is no data, i cant add data also...There is no data connection, do i have to make one first?...i am using vb 2010 express.....

Submitted by Anonymous (not verified) on Mon, 09/27/2010 - 22:26

Gud day sir Please I want to know how I can edit and save records into the database. Please i created a form whereby the user will enter a password and will click on a button and the next form will display, but i have tried "Close() code" but it didn't work, please what should I do? Thanks Regards, Chioma [email protected] reply

Submitted by Anonymous (not verified) on Mon, 10/18/2010 - 23:36

using the open.record.set command in excel VB I want to find the access record to update and then pass the data to the appropriate fields. Here is the code that doesn't work. It appears not to have bugs - but then there is nothing happening - no updates. I should mention that the "AddNew" part works just not the "edit" part that is down further. Sub Export_tear_data() ' ' Upload Macro ' Macro recorded 11/18/2009 by LAB1 ' Sheets("Summary").Select Range("A5").Select Sheets("Summary").Select ActiveWorkbook.save 'Application.Quit Dim db As Database, rs As Recordset, r As Long, sa As Recordset Set db = OpenDatabase("X:\PAPLab\PAPLabTables.mdb") ' open the database ' get all records in a table 'Start Data Transfer Set sa = db.OpenRecordset("ResultTens", dbOpenTable) r = 7 ' the start row in the worksheet Do While Len(Range("A" & r).Value) > 0 ' repeat until first empty cell in column A If Range("Q" & r).Value = "- No -" And Len(Range("c" & r).Value) > 0 Then ' Checks if record exists 'With sa sa.AddNew If IsNumeric(Range("B" & r).Value) Then sa.Fields("ResultTensSampleNo") = Range("B" & r).Value End If If IsNumeric(Range("C" & r).Value) Then sa.Fields("ResultTensElmMDR1") = Range("C" & r).Value End If If IsNumeric(Range("D" & r).Value) Then sa.Fields("ResultTensElmMDR2") = Range("D" & r).Value End If If IsNumeric(Range("E" & r).Value) Then sa.Fields("ResultTensElmMDR3") = Range("E" & r).Value End If If IsNumeric(Range("F" & r).Value) Then sa.Fields("ResultTensElmMDR4") = Range("F" & r).Value End If If IsNumeric(Range("G" & r).Value) Then sa.Fields("ResultTensElmMDR5") = Range("G" & r).Value End If If IsNumeric(Range("J" & r).Value) Then sa.Fields("ResultTensElmTDR1") = Range("J" & r).Value End If If IsNumeric(Range("K" & r).Value) Then sa.Fields("ResultTensElmTDR2") = Range("K" & r).Value End If If IsNumeric(Range("L" & r).Value) Then sa.Fields("ResultTensElmTDR3") = Range("L" & r).Value End If If IsNumeric(Range("M" & r).Value) Then sa.Fields("ResultTensElmTDR4") = Range("M" & r).Value End If If IsNumeric(Range("N" & r).Value) Then sa.Fields("ResultTensElmTDR5") = Range("N" & r).Value End If 'Upload Arm Weight If IsNumeric(Range("R" & r).Value) Then sa.Fields("ResultTensElmMDArm") = Range("R" & r).Value End If If IsNumeric(Range("S" & r).Value) Then sa.Fields("ResultTensElmTDArm") = Range("S" & r).Value End If sa.Update End If 'If record for sample already exists If Range("Q" & r).Value = "- Yes -" Then sa.Edit If IsNumeric(Range("B" & r).Value) Then sa.Fields("ResultTensSampleNo") = Range("B" & r).Value End If If IsNumeric(Range("C" & r).Value) Then sa.Fields("ResultTensElmMDR1") = Range("C" & r).Value End If If IsNumeric(Range("D" & r).Value) Then sa.Fields("ResultTensElmMDR2") = Range("D" & r).Value End If If IsNumeric(Range("E" & r).Value) Then sa.Fields("ResultTensElmMDR3") = Range("E" & r).Value End If If IsNumeric(Range("F" & r).Value) Then sa.Fields("ResultTensElmMDR4") = Range("F" & r).Value End If If IsNumeric(Range("G" & r).Value) Then sa.Fields("ResultTensElmMDR5") = Range("G" & r).Value End If If IsNumeric(Range("J" & r).Value) Then sa.Fields("ResultTensElmTDR1") = Range("J" & r).Value End If If IsNumeric(Range("K" & r).Value) Then sa.Fields("ResultTensElmTDR2") = Range("K" & r).Value End If If IsNumeric(Range("L" & r).Value) Then sa.Fields("ResultTensElmTDR3") = Range("L" & r).Value End If If IsNumeric(Range("M" & r).Value) Then sa.Fields("ResultTensElmTDR4") = Range("M" & r).Value End If If IsNumeric(Range("N" & r).Value) Then sa.Fields("ResultTensElmTDR5") = Range("N" & r).Value End If sa.Update End If 'MsgBox ("Sample " & Range("A" & r).Value & " already exists in the tensile data table. ") 'DoEvents sa.MoveLast r = r + 1 ' next row Loop sa.Close Set sa = Nothing

Submitted by Anonymous (not verified) on Sat, 10/30/2010 - 08:34

Hello. I have wasted a lot of time reading and searching the internet for answers to my apparently simple problem. I remembered your site and, tonight, while searching for answers, I stumble on your site by accident -- thank's to Bing. Anyway, all the examples I run into don't deal with tables that have Primary Keys. I have a small VisualBasic 2010 application that writes to an MS-Access database (*.mdb). Inserting records works, and I had to search hard how to retrieve the Primary Key after insert completed to be able to have it to use as a Foreign Key in my related table. So, my database is very easy, it has only two tables with a one to many relationship. When I want to update the records, I use the DataAdapter to fill a DataSet. Then I can display the records easily. But the DataAdapter Update method does not work for me, after I make change the fields and want to persist to the DataSource (my MS-Access database). I suspect VisualBasic will not update the database unless it is instructed specifically on how to deal with the Primary Key issue? At one point, I suspected I have to first update the child table (even though I make no changes to it) in order for the Update on the Parent table to work. But that does not make sense. I do use CommandBuilder. [email protected]

Submitted by admin on Sat, 10/30/2010 - 19:45

What do you mean by itemdata?

Submitted by Anonymous (not verified) on Sat, 10/30/2010 - 22:41

itemdata for combobox and listbox in .NET. obsolete poh kasi itemdata sa .NET..

Submitted by Anonymous (not verified) on Tue, 12/21/2010 - 00:14

i need coding of insert ,update, and delete...... in c sharp plzzzzzzzzzzz help

Submitted by ced on Tue, 12/21/2010 - 18:16

look for my work.. i have a simple add,edit in c#... "simple database manipulation in c#" point to search engine!!!!!!!!!!!

Submitted by Anonymous (not verified) on Thu, 01/13/2011 - 17:54

I NEED how u add delete records in database from frontend,in vb.net pls hepl me for this my email :[email protected]

Submitted by Anonymous (not verified) on Fri, 01/21/2011 - 22:09

i need delete record from ms access in front end.. pls tel this codngs... [email protected],plz..plz..

Submitted by Anonymous (not verified) on Fri, 01/28/2011 - 21:30

hello any body that can help me pls I successfully insert the record from my from to the ms access database using vb6 with the datagrid the problem is unable to update the autoNumber and unable to delete and update the record as needed. the code that I use for deleting the record is Dim iResponce As Integer iResponce = MsgBox("Do you want to Delete this record?", vbOKCancel + vbQuestion, "Delete") If iResponce = vbOK Then rs.Delete txtOffice_code.Text = "" CboOffice_name.Text = "" Else rs.CancelUpdate txtOffice_code.Enabled = False CboOffice_name.Enabled = False

this is a single program of all there stuff...................... using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Data.SqlClient; using System.Windows.Forms; namespace CompleteADO { public partial class Form1 : Form { SqlDataAdapter da; DataSet ds; SqlCommand com; SqlConnection con; DataTable dt; int curr = 0; int totr = 0; public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string constring = @"Data Source=DEEPAK-7FD6C5E5\MANISH;Initial Catalog=manishv;Integrated Security=true"; con = new SqlConnection(constring); con.Open(); com = new SqlCommand("Select * from customer", con); da = new SqlDataAdapter(com); ds = new DataSet(); da.Fill(ds, "cust"); dt = ds.Tables["cust"]; curr = 0; totr = dt.Rows.Count; fillcontrol(); initlizecomm(); togglecontrol(true); } public void fillcontrol() { textBox1.Text = dt.Rows[curr]["cid"].ToString(); textBox2.Text = dt.Rows[curr]["nm"].ToString(); textBox3.Text = dt.Rows[curr]["loc"].ToString(); } private void button2_Click(object sender, EventArgs e) { curr++; if (curr >= totr) curr = 0; fillcontrol(); } private void button3_Click(object sender, EventArgs e) { curr--; if (curr 0) curr = totr - 1; fillcontrol(); } public void initlizecomm() { da.InsertCommand = con.CreateCommand(); da.InsertCommand .CommandText ="insert into customer"+"(cid,nm,loc)"+"values(@cid,@nm,@loc)"; addparams(da.InsertCommand, "cid", "nm", "loc"); da.UpdateCommand = con.CreateCommand(); da.UpdateCommand.CommandText = "update customer set " + "cid=@cid,nm=@nm,loc=@loc " + "where cid=@cid"; addparams(da.UpdateCommand, "cid", "nm", "loc"); da.DeleteCommand = con.CreateCommand(); da.DeleteCommand.CommandText = "delete customer where cid=@cid"; addparams(da.DeleteCommand, "cid"); } public void addparams(SqlCommand com, params string[] col) { foreach (string co in col) { com.Parameters.Add("@" + co, SqlDbType.Char, 0, co); } } public void togglecontrol(bool val) { textBox1.ReadOnly = val; textBox2.ReadOnly = val; textBox3.ReadOnly = val; button1.Enabled = val; button2.Enabled = val; button3.Enabled = val; button4.Enabled = val; button5.Enabled = val; button6.Enabled = val; button7.Enabled = !val; button8.Enabled = !val; } private void button4_Click(object sender, EventArgs e) { togglecontrol(false); } private void button7_Click(object sender, EventArgs e) { DataRow dr = dt.Rows[curr]; dr.BeginEdit(); dr["cid"] = textBox1.Text; dr["nm"] = textBox2.Text; dr["loc"] = textBox3.Text; dr.EndEdit(); da.Update(ds, "cust"); ds.AcceptChanges(); togglecontrol(true); } private void button5_Click(object sender, EventArgs e) { DataRow dr = dt.NewRow(); dt.Rows.Add(dr); totr = dt.Rows.Count; curr = totr - 1; dr["cid"] = totr; textBox1.Text = totr.ToString() ; textBox2.Text = ""; textBox3.Text = ""; togglecontrol(false); } private void button6_Click(object sender, EventArgs e) { DataRow dr = dt.Rows[curr]; dr.Delete(); da.Update(ds, "cust"); ds.AcceptChanges(); totr--; curr = totr - 1; fillcontrol(); } } }

Submitted by Anonymous (not verified) on Sun, 02/13/2011 - 23:24

sir, I can't find the step by step process in your zip file. please sir, i need the documentation on how to do it from start to finish. Im new in this kind of programming but i really want to learn. im using visual basic in visual studio express 2010. hoping for your kindness. Thank you so much!

Submitted by Anonymous (not verified) on Fri, 03/04/2011 - 16:06

Hi, I want to make two function on single button(Say Update and diplaying textBox). On first click it should shows textbox, which is panel after click on panel's button(say OK) the first button(Update) run second function(Say it get Updated). Plz help me. :)