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

Submitted by admin on Tuesday, March 10, 2009 - 15:01.

Language

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.

Comments

vb6

can u make same program in vb6?

re: vb6

Search for multi column combobox above. The concept is the same with this program.

SIR

If TextBox1.Text > "" And TextBox2.Text > "" And TextBox3.Text > "" And TextBox4.Text > "" And TextBox5.Text > "" Then con.Open() Dim up As String = "Update guadalupe set DR=@DR,address=@address,description=@description,price=@price,petsa=@petsa" Dim cmd As New OleDbCommand(up, con) cmd.Parameters.Add(New OleDbParameter("@DR", OleDbType.VarChar, 15)).Value = TextBox1.Text 'cmd.Parameters.Add(New OleDbParameter("@DR", OleDbType.VarChar, 15)).Value = TextBox1.Text cmd.Parameters.Add(New OleDbParameter("@address", OleDbType.VarChar, 15)).Value = TextBox2.Text cmd.Parameters.Add(New OleDbParameter("@description", OleDbType.VarChar, 15)).Value = TextBox3.Text cmd.Parameters.Add(New OleDbParameter("@price", OleDbType.VarChar, 15)).Value = TextBox4.Text cmd.Parameters.Add(New OleDbParameter("@petsa", OleDbType.VarChar, 15)).Value = TextBox5.Text cmd.ExecuteNonQuery() con.Close() MsgBox("Successfully updated") Else MsgBox("Please complete the form") End If MY CODE IN UPDATING IT WORKS BUT THE PROBLEM IS ALL ROWS WERE UPDATED AND CHANGES EVERYTHING.. PLEASE I NEED SOME ADVICE

i read ur code ...and there

i read ur code ...and there is a problem that there is needed a 'where ' clause to update a specific row , that's why ur code updating all rows. ex: Update table1 set name='xyz' where id='123'

hi gud pm., can i ask? how

hi gud pm., can i ask? how can i run the program that ive download already?? i need ur reply now., thank you..

re: hi gud pm., can i ask? how

Don't be too rush when asking question. You need to be polite sometimes to be answered.

You need vb.net to run this source code.

find record from database

do you have simple code in finding record in the database?

re: find record from database

That's pretty simple. Just add a textbox and modify the SQL string. If you still don't know how to do this then reply to this msg and I will create one for you if I have time.

finding a record

please post the coding.....for finding a record using datagrid or textbox....

re: finding a record

Browse Visual Basic .NET section. I have already done this.

microsoft visual studio 2005-code for add,delete,edit and save

Hi sir, i just view the video about how to add,edit,save,delete,and search.. can you teach me sir what are the codes for that buttons because i really want to know sir on what are the codes for those buttons sir for my studies. I'm just a second year college student willing to know about visual basic studio 2005. Pls. answer me sir or pls help me sir.. Thank you Sir, i will be waiting for your respond.. just send it to me sir in my e-mail [email protected]

gud day sir!!

can you give us any idea how to use query using vb.net and access?? thank you sir..

vb.net Version

I have been coding for several years and now using vb.net 2008. When I try to run your code I get an error on Object reference not set to an instance of an object,

newRow = dt.NewRow() Line 98 I set dt = new datatable because 99% of the time when I see this error it is because the the instance was not created as a new instance. Any Ideas? What version did you code this in ? Thanks Dwain

re: vb.net Version

I'm using vb.net 2008, too. I tested it several times without error.

thankyou for the code

THANK for this code, , ,,

Thank for this code it help us, , , and i was edited this program, , to improve it. Thanks you very much, , , JHERVY

vb 2008

Hi! It's been a while since I coded something and that was VB6 then. Do you have time to share some codes/programs using VB2008 as I am using it now. I just need to refresh my knowledge. thank you. My email: yan(at)yanspeaks(dot)com Thank you kabayan!

re: vb 2008

Hi kababayan, Yes you can download the hotel reservation system, enrollment system and you can also study some of my tutorials about .net.

vb2008

hi, your source code is very userful as im making a project in vb.net2008 with access database.its really very appriciated.will u plz upload the coding of granting the permisiion of different user for accessing the software or data or it send on my id '[email protected]' Thanx, Regards, Sandeep

re: vb2008

Please use the code in hotel reservation system. It has a user permission.

vb2008(list view)

hi, Thanx for the replied.I want to ask that how the data is view through the list view.I mean like customer id shown in list view and when user click on the id,it shows the full detail of that customer in different fields that fields like cus id,name ,add.i saw ur list view project.but in this fields are also shown from database.i just want to show only data from database and not the fields name.thats field name,i put throgh the components like textboxes.labels and button.will u plz tell how its done,when user clicked on cust id in list view and records would be shown of that customer. Thanx, Regards, Sandeep

re: vb2008(list view)

There's a lot of sample of this in almost of my code. Download it and study how it works.

Hello Joomar

Hello, Could you tell me how to be strong in vb.net.I started in vb.net from beginner,but I don't where should i begin. Anyway Could you tell me which book should i take a look for the first time? or any website ? Thank

re: Hello Joomar

If you want to become a professional vb.net programmer I suggest you take a look at the Books section above. You may also wish to study "Database Programming Made Easy".

Hello Jomar

Anyway,would you like tell me where should i pick up that book"Database Programming Make Easy"? I really interesting with your programming code.On the other hand How long did you study with it? where could i finding that course? Thank you for helping me!

I suggest you take a loot at

I suggest you take a loot at this book Beginning VB 2008 Databases: From Novice to Professional. Since I'm not new to VB I leart VB.NET almost a month after studying its concept.

retrive image!!!

Hi, Follwing is the coding of save and retrive the image from ms access database in vb.net2008.but its through input box.I just want that user click on a account no.from combobox and image is displayed.

Imports System.Data.OleDb
Imports System.IO
 
Public Class Form7
Public ImageSlno As Integer
Public con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source =C:\Documents and Settings\Pankaj Arora\My Documents\database.mdb") ' connection srting 
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Call Insert_Image()
End Sub
 
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Try
OpenFileDialog1.Filter = "Bmp Files(*.bmp)|*.bmp|Gif Files(*.gif)|*.gif|Jpg Files(*.jpg)|*.jpg"
OpenFileDialog1.ShowDialog()
PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
Catch
 
End Try
End Sub
Public Sub Insert_Image()
Try
Dim st As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
Dim mbr As BinaryReader = New BinaryReader(st)
Dim buffer(st.Length) As Byte
mbr.Read(buffer, 0, CInt(st.Length))
st.Close()
con.Open()
Dim Str As String = "insert into imageTable(photo) values(?)"
Dim Cmd As New System.Data.OleDb.OleDbCommand(Str, con)
Cmd.Parameters.Add("@photo", System.Data.OleDb.OleDbType.Binary, buffer.Length).Value = buffer
Cmd.ExecuteNonQuery()
con.Close()
MsgBox("Image Saved Successfully")
Catch ex As Exception
con.Close()
MsgBox("Error In Insertphoto", MsgBoxStyle.Critical, "Error")
MsgBox(ex.ToString)
End Try
End Sub
 
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
ImageSlno = InputBox("Insert Slno ", , "0")
 
Call LoadPhoto(ImageSlno)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Sub LoadPhoto(ByVal slno As Decimal)
' display image from the database
Try
Dim str As String = "SELECT photo FROM ImageTable WHERE slno = " & slno
con.Open()
Dim cmd As New OleDb.OleDbCommand(str, con)
Dim b() As Byte
b = cmd.ExecuteScalar()
con.Close()
If (b.Length > 0) Then
Dim stream As New MemoryStream(b, True)
stream.Write(b, 0, b.Length)
DrawToScale(New Bitmap(stream))
stream.Close()
End If
Catch ex As Exception
'MsgBox(ex.ToString)
End Try
End Sub
'Function to Create Instance For the Image From the Buffer
Private Sub DrawToScale(ByVal bmp As Image)
PictureBox1.Image = New Bitmap(bmp)
End Sub
 
Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
End Sub
End Class

Plz anybody help me.its urgent. Thanx, Regards, Sandeep

Use the following code.

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Try
            ImageSlno = ComboBox1.Text
 
            Call LoadPhoto(ImageSlno)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

But I'm still wondering why you ask this. Because it is very easy. With your code above it seems that you know this already.

hi, How to insert a data

hi, How to insert a data into two table by using acces database with vb.net Thanx, Regards, Sandeep

Kindly study Database

Kindly study Database Programming Made Easy. I already discuss everything in there.

Upadte Database in webserver

Hi friend i already download your Code. But i want to clear that can i able to access Msdatabase File from web server. if possible please explain me my id is [email protected]

re: Upadte Database in webserver

Are you planning to create a website using ms access database? Yes this is possible to access ms database from your web server.

Manipulation of datasource of report viewer

Gud day sir! i want to seek help on how to change the datasource of a report in the choice of the user dynamically.. in short.. one report viewer, multiple rdlc files can be shown simultaneously.. hust a sample code.. hope for your kind help! more power and godbless! tnx.. aguirre

hi bro..

i have two tables using acces database and i need to insert the same records in that two particular tables.The problem is that, "I can only save the records into one table and the other table is empty.".I hope you can help me with this particular problem, i'll apreciated it so much..thank you.. if its ok, heres my eadd: [email protected]

re: hi bro..

gives use some snapshot of you work.

ASP.NET

sir..gud am...can u teach me the hard coding connection of my project to ms access as my back end with out using any control...purely coding???...adding record in table updating retrieving and deleting...thx in advance...

for save in ms .access data base

hi i am already design the code using vb.net 2005 for insert data in the table , but when i am again run that program then all the previous value of the table will delete and again newly entry value are store in table my code: Imports System.Data.OleDb Public Class Form1 Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\raghu1stdb.mdb") 'Dim da As OleDbDataAdapter Dim da As New OleDbDataAdapter("select * from table1", con) 'Dim x As String = "select *from table1" ' da = New OleDbDataAdapter(x, con) Dim ds As New DataSet, dt As New DataTable, dr As DataRow Dim i As Integer Dim bm As BindingManagerBase : Dim cb As OleDbCommandBuilder Dim cm As OleDbCommand Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim x As String = "select *from table1 " da = New OleDbDataAdapter da.SelectCommand = New OleDbCommand(x, con) da = New OleDbDataAdapter(x, con) ds = New DataSet da.Fill(ds, "name1") dt = ds.Tables("name1") dr = dt.NewRow bm = Me.BindingContext(dt) cb = New OleDbCommandBuilder(da) ''MessageBox.Show() End Sub Private Sub Bsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Bsave.Click dr = dt.NewRow dr.Item("ID") = Txtid.Text dr.Item("Name") = txtname.Text dr.Item("Address") = txtadd.Text da.Fill(ds, "name1") dt = ds.Tables("name1") dt.Rows.Add(dr) dt.AcceptChanges() da.Update(dt) MessageBox.Show("datais update") End Sub End Class so what i will do plz send the code for save in db using vb.net as soon as posible plz send me i need that plz send in my mail id: [email protected]

Kindly download one of the

Kindly download one of the code in Database Programming Made Easy. I have already done this.

hello

hi,, i copied your code about how to add update delete,,, the one thats above,,,, after ive done everything already (including the module, forms, designs, everything) it shows an error "Error while trying to run your project. could not load file or assembly 'add,deletewithaccess' or one of its dependencies. the given assembly name or code was invalid. (exception from HRESULT:0x80131047)" by the way 'add,deletewithaccess' is the filename of the project i saved in... please help....

re: hello

This is a strange error. May I know what version of VB.NET you are using?

help pls.. urgent..

i dont know how to insert record in access database. i have a formCustomer to that has txtLastName, txtFirstName and txtContact.. when i click the add button it should add the record in the database then i should view the list in the main form.. your code is quite hard to understand because im only a beginner. and is it ok to insert the record from 1 form to another or there are codes needed so that i can access it to the other form.? here is my code. Private Sub btnAccept_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAccept.Click Dim con As New OleDb.OleDbConnection con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\YayaDB.mdb" con.Open() Dim query = "INSERT INTO Customer(Last Name,First Name, Contact No) VALUES(@LastName, @FirstName, @Contact)" Dim cmd = New OleDbCommand(query, con) cmd.Connection = con cmd.Parameters.AddWithValue("@LastName", txtLastName.Text) cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text) cmd.Parameters.AddWithValue("@Contact", txtContactNo.Text) cmd.ExecuteNonQuery() con.Close() Me.Close() End Sub

Add new comment

Add new comment
2309 views