• (2 Pages)
  • +
  • 1
  • 2

Using DataGridView with Access All using code, no VS database components Rate Topic: ***-- 2 Votes

#1 Sethro117  Icon User is offline

  • Still the sexiest mofo.
  • member icon

Reputation: 236
  • View blog
  • Posts: 2,378
  • Joined: 14-January 09

Posted 06 January 2010 - 02:19 PM

*
POPULAR

For me, I dont like using the tools VS provides that write your databse code for you. I prefer to write all my code out that does my Inserts, Updates, Adding and Deleting values to the database. Also, anytime you’re using OleDB, or SQL or any type of connection be sure you import the namespace you will be needing. For OleDB we will be using Imports System.Data.OleDb

Form 1(frmMain) - The Main Form
btnNew - New Record Button
btnUpdate - Update Record Button
btnDelete - Delete Record Button
dgv1 - DataGridView for Records
txtSearch - Search Records
Posted Image

First to connect to any Database source you have to setup your connection string. I usually go with Access or SQL Server. If you are looking into using another datasource check out www.connectionstrings.com for other data sources like MySQL, Oracle and lots more.
Imports System.Data.OleDb
Public Class frmMain
	' Our Connection String - Be sure you change the Data Source
	Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Sethro\Desktop\Test.mdb")


Now we go into selecting our records from the Data Source and populating out DataGridView with them.
	' This displays our records in the DataGridView so the User can select them to update, delete, or search
	Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		' Our SQL Statement
		Dim sql As String
		sql = "SELECT * FROM users"
		' This is our DataAdapter. This executes our SQL Statement above against the Database
		' we defined in the Connection String
		Dim adapter As New OleDbDataAdapter(sql, con1)
		' Gets the records from the table and fills our adapter with those.
		Dim dt As New DataTable("users")
		adapter.Fill(dt)
		' Assigns our DataSource on the DataGridView
		dgv1.DataSource = dt
		'
		Dim sql1 As String
		sql1 = "SELECT * FROM users"
		Dim adapter1 As New OleDbDataAdapter(sql1, con1)
		Dim cmd1 As New OleDbCommand(sql1, con1)
		'Dim dt1 As New DataTable("users")
		con1.Open()
		Dim myreader As OleDbDataReader = cmd1.ExecuteReader
		myreader.Read()

		con1.Close()
	End Sub


Now that we have our records, and an application to interact with our database, we don’t have to open the data file and edit it. We can show the New Record form and we can add records from it. The code to the New Record form(Form2) is below. We also have our update button and delete button. With our Update it allows us to select a record from the DataGridView, then we can click the Update button and the Update Record form shows with the corresponding information from the selected row in the DataGridView.

You can also double click the record and it will open the Update Record form with the corresponding information.
	Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
‘ Shows the New Record form(Form2)
		frmNew.Show()
	End Sub

	Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
		frmUpdate.Show()
		' What this does is when the User selects a record in the DataGridView
		' it will populate the textbox on the other form with the corresponding
		' Cell Value.
		frmUpdate.txtFirst.Text = dgv1.CurrentRow.Cells(0).Value.ToString
		frmUpdate.txtLast.Text = dgv1.CurrentRow.Cells(1).Value.ToString
		frmUpdate.txtAddress.Text = dgv1.CurrentRow.Cells(2).Value.ToString
		frmUpdate.txtCity.Text = dgv1.CurrentRow.Cells(3).Value.ToString
		frmUpdate.txtZip.Text = dgv1.CurrentRow.Cells(4).Value.ToString
		frmUpdate.txtPhone.Text = dgv1.CurrentRow.Cells(5).Value.ToString
		frmUpdate.txtEmail.Text = dgv1.CurrentRow.Cells(6).Value.ToString
		frmUpdate.txtID.Text = dgv1.CurrentRow.Cells(7).Value.ToString
	End Sub

	Private Sub dgv1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv1.CellDoubleClick
		frmUpdate.Show()
		' Same thing as btnUpdate_Click except the user can actually DoubleClick
		' a record. You want to be sure you get the field names and cell numbers 
		‘ correct or the information will be misconstrued. 
		frmUpdate.txtFirst.Text = dgv1.Rows(e.RowIndex).Cells(0).Value.ToString
		frmUpdate.txtLast.Text = dgv1.Rows(e.RowIndex).Cells(1).Value.ToString
		frmUpdate.txtAddress.Text = dgv1.Rows(e.RowIndex).Cells(2).Value.ToString
		frmUpdate.txtCity.Text = dgv1.Rows(e.RowIndex).Cells(3).Value.ToString
		frmUpdate.txtZip.Text = dgv1.Rows(e.RowIndex).Cells(4).Value.ToString
		frmUpdate.txtPhone.Text = dgv1.Rows(e.RowIndex).Cells(5).Value.ToString
		frmUpdate.txtEmail.Text = dgv1.Rows(e.RowIndex).Cells(6).Value.ToString
		frmUpdate.txtID.Text = dgv1.Rows(e.RowIndex).Cells(7).Value.ToString
	End Sub


Below is the code for our Delete Record function. You are able to select a record and hit the Delete button and the record will be deleted from both the DataGridView and the data source file. Be sure you have a Unique ID or Field in your Data file so you can use the WHERE statement to be sure that the correct record is deleted.
	Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
' This is our DELETE Statement. To be sure we delete the correct record and not all of 
‘ them.
		' We use the WHERE to be sure only that record that the user has selected is deleted.
		Dim sqldelete As String
		sqldelete = "DELETE * FROM users WHERE IDNum='" & dgv1.CurrentRow.Cells(7).Value.ToString & "'"
		' This is our DataAdapter. This executes our SQL Statement above against the Database
		' we defined in the Connection String
		Dim adapter As New OleDbDataAdapter(sqldelete, con1)
		' Gets the records from the table and fills our adapter with those.
		Dim dt As New DataTable("users")
		adapter.Fill(dt)
		' Assigns the edited DataSource on the DataGridView and the refreshes the 
		' view to ensure everything is up to date in real time.
		dgv1.DataSource = dt
		‘ This is a Sub in Module 1 to refresh the DataGridView when information is added,
 		‘  updated, or deleted.
		RefreshDGV()
	End Sub


Now is the important part. When a user has a bunch of records they want to sort though they needto be able to Search for what they are looking for without having to browse through all the records. Below is the code for our Search function.
	Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
		' SQL Statement so our User can search for either FirstName or LastName
		Dim sqlsearch As String
		sqlsearch = "SELECT * FROM users WHERE FirstName LIKE '%" & txtSearch.Text & "%'" & " OR LastName LIKE '%" & txtSearch.Text & "%'"
		' Once again we execute the SQL statements against our DataBase
		Dim adapter As New OleDbDataAdapter(sqlsearch, con1)
		' Shows the records and updates the DataGridView
		Dim dt As New DataTable("users")
		adapter.Fill(dt)
		dgv1.DataSource = dt
		'
	End Sub
End Class



Now the code for the New Record Form to create new records.

Form 2(frmNew) - New Record Form
txtFirst
txtLast
txtAddress
txtCity
txtZip
txtPhone
txtEmail
txtID
btnSave
Posted Image

Imports System.Data.Oledb
Public Class frmNew
	' Our Connection String
	Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Sethro\Desktop\Test.mdb")


The Save button is what saves the new information to our Data source.
	Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
		Dim sqlinsert As String
		‘ We use the INSERT statement which tells our program to add the information
		‘ from the Forms Text fields into the Databases columns. 
		sqlinsert = "INSERT INTO users(FirstName, LastName, Address, City, Zip, Phone, Email, IDNum)" & _
		"VALUES(@FirstName, @LastName, @Address, @City, @Zip, @Phone, @Email, @IDNum)"
		Dim cmd As New OleDbCommand(sqlinsert, con1)
		' This assigns the values for our columns in the DataBase. 
		' To ensure the correct values are written to the correct column
		cmd.Parameters.Add(New OleDbParameter("@FirstName", txtFirst.Text))
		cmd.Parameters.Add(New OleDbParameter("@LastName", txtLast.Text))
		cmd.Parameters.Add(New OleDbParameter("@Address", txtAddress.Text))
		cmd.Parameters.Add(New OleDbParameter("@City", txtCity.Text))
		cmd.Parameters.Add(New OleDbParameter("@Zip", txtZip.Text))
		cmd.Parameters.Add(New OleDbParameter("@Phone", txtPhone.Text))
		cmd.Parameters.Add(New OleDbParameter("@Email", txtEmail.Text))
		cmd.Parameters.Add(New OleDbParameter("@IDNum", txtID.Text))
		' This is what actually writes our changes to the DataBase.
		' You have to open the connection, execute the commands and
		' then close connection.
		con1.Open()
		cmd.ExecuteNonQuery()
		con1.Close()
		' This are subs in Module1, to clear all the TextBoxes on the form
		' and refresh the DataGridView on the MainForm to show our new records.
		ClearTextBox(Me)
		RefreshDGV()
		Me.Close()
	End Sub
End Class



Now our code for the Update form to update any records.

Form 3(frmUpdate) - Update Record Form
txtFirst
txtLast
txtAddress
txtCity
txtZip
txtPhone
txtEmail
txtID
btnUpdate
Posted Image

Imports System.Data.Oledb
Public Class frmUpdate
	' Our Connection String
	Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Sethro\Desktop\Test.mdb")



The code for the Update button.
	Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
		Dim sqlupdate As String
		‘ Here we use the UPDATE Statement to update the information. To be sure we are 
		‘ updating the right record we also use the WHERE clause to be sureno information
		‘ is added or changed in the other records
		sqlupdate = "UPDATE users SET FirstName=@FirstName, LastName=@LastName, " & _
		"Address=@Address, City=@City, Zip=@Zip, Phone=@Phone, Email=@Email WHERE IDNum='" & txtID.Text & "'"
		Dim cmd As New OleDbCommand(sqlupdate, con1)
		' This assigns the values for our columns in the DataBase. 
		' To ensure the correct values are written to the correct column
		cmd.Parameters.Add(New OleDbParameter("@FirstName", txtFirst.Text))
		cmd.Parameters.Add(New OleDbParameter("@LastName", txtLast.Text))
		cmd.Parameters.Add(New OleDbParameter("@Address", txtAddress.Text))
		cmd.Parameters.Add(New OleDbParameter("@City", txtCity.Text))
		cmd.Parameters.Add(New OleDbParameter("@Zip", txtZip.Text))
		cmd.Parameters.Add(New OleDbParameter("@Phone", txtPhone.Text))
		cmd.Parameters.Add(New OleDbParameter("@Email", txtEmail.Text))
		' This is what actually writes our changes to the DataBase.
		' You have to open the connection, execute the commands and
		' then close connection.
		con1.Open()
		cmd.ExecuteNonQuery()
		con1.Close()
		' This are subs in Module1, to clear all the TextBoxes on the form
		' and refresh the DataGridView on the MainForm to show our new records.
		ClearTextBox(Me)
		Me.Close()
		RefreshDGV()
	End Sub
End Class



Module 1 holds the code for the RefreshDGV and ClearTextBox Subs.
Imports System.Data.OleDb
Module Module1
	Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Sethro\Desktop\Test.mdb")
	Sub RefreshDGV()
		Dim sql As String
		sql = "SELECT * FROM users"
		Dim adapter As New OleDbDataAdapter(sql, con1)
		Dim dt As New DataTable("users")
		adapter.Fill(dt)
		frmMain.dgv1.DataSource = dt
	End Sub

	Sub ClearTextBox(ByVal FormName As Form)
		For Each txt As Control In FormName.Controls
			If TypeOf txt Is TextBox Then
				CType(txt, TextBox).Text = ""
			End If
		Next
	End Sub
End Module



Ive attached the project file so you can open it up and see all the action for yourself.
[attachment=15608:attachment]

Is This A Good Question/Topic? 7
  • +

Replies To: Using DataGridView with Access

#2 IndyLateNite  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 22-January 10

Posted 26 January 2010 - 03:03 PM

Thank you for this tutorial. It really helped me get started on my project and clear up some of this vb.net fog in my head.
Was This Post Helpful? 0
  • +
  • -

#3 Needanswer  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 61
  • Joined: 27-January 10

Posted 27 January 2010 - 07:20 PM

I did use your code to do my project, but I got message on the cmd.ExecuteNonQuery() - System.Data.OleDb.OleDbException, type mismatch. Actually my project is on the vb 2008 express, I don't know what is wrong? PLEASE HELP, THANKS.

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
Dim sqlinsert As String
' We use the INSERT statement which tells our program to add the information
' from the Forms Text fields into the Databases columns.
sqlinsert = "INSERT INTO sysdep(id, sysaccount,syspw)" & _
"VALUES(@FirstName, @LastName, @syspw)"
Dim cmd As New OleDbCommand(sqlinsert, con1)

cmd.Parameters.Add(New OleDbParameter("@id", TextBox1.Text))
cmd.Parameters.Add(New OleDbParameter("@sysaccount", TextBox2.Text))
cmd.Parameters.Add(New OleDbParameter("@syspw", TextBox3.Text))

con1.Open()
cmd.ExecuteNonQuery() ---------------System.Data.OleDb.OleDbException, type mismatch
con1.Close()
End Sub

Actually, I only have id, sysaccount, syspw for my project. Let me know how to solve? THANKS.
Was This Post Helpful? 0
  • +
  • -

#4 Needanswer  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 61
  • Joined: 27-January 10

Posted 28 January 2010 - 06:46 PM

I already solve this problem, THANKS.
Was This Post Helpful? 1
  • +
  • -

#5 Needanswer  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 61
  • Joined: 27-January 10

Posted 29 January 2010 - 01:34 AM

Dim sqlupdate As String

' sqlupdate = "UPDATE sysdep SET sysaccount= @sysaccount, syspw= @syspw, " & _
'"WHERE id='" & TextBox1.Text & "'"
sqlupdate = "UPDATE sysdep SET sysaccount= @sysaccount, syspw= @syspw WHERE id= @id"

Dim cmd1 As New OleDbCommand(sqlupdate, con1)

cmd1.Parameters.Add(New OleDbParameter("@id", TextBox1.Text))
cmd1.Parameters.Add(New OleDbParameter("@sysaccount", TextBox2.Text))
cmd1.Parameters.Add(New OleDbParameter("@syspw", TextBox3.Text))
Debug.Print(TextBox1.Text)
Debug.Print(TextBox2.Text)
Debug.Print(TextBox3.Text)

Try
con1.Open()
cmd1.ExecuteNonQuery()
Catch ex As OleDbException
MessageBox.Show(ex.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

con1.Close()


RefreshDGV()

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""

This update is not working, WHY???????? THANKS.
Was This Post Helpful? 0
  • +
  • -

#6 Needanswer  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 61
  • Joined: 27-January 10

Posted 29 January 2010 - 01:39 AM

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Dim sqldelete As String
sqldelete = "DELETE * FROM sysdep WHERE id='" & DataGridView1.CurrentRow.Cells(0).Value.ToString & "'"

Dim adapter As New OleDbDataAdapter(sqldelete, con1)

Dim dt1 As New DataTable("sysdep")
adapter.Fill(dt1) ---------ERROR, TYPE MISMATCH

DataGridView1.DataSource = dt1

RefreshDGV()

'TextBox1.Text = ""
'TextBox2.Text = ""
'TextBox3.Text = ""

End Sub

I GOT ERROR ON THE adapter.Fill(dt1), need HELP????? THANKS.
Was This Post Helpful? 0
  • +
  • -

#7 gixty  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 29-January 10

Posted 29 January 2010 - 01:02 PM

Great thanks!! I was looking for this :D

One question, I implemented everything in the frmMain and Using SQL instead of OleDB. Now I want to keep my textboxes empty as long as the search results is 0
How can I do that?

This post has been edited by gixty: 29 January 2010 - 01:09 PM

Was This Post Helpful? 0
  • +
  • -

#8 Needanswer  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 61
  • Joined: 27-January 10

Posted 31 January 2010 - 10:18 PM

Solve this problem ON POST #5, THANKS.

This post has been edited by Needanswer: 31 January 2010 - 10:19 PM

Was This Post Helpful? 1
  • +
  • -

#9 fordraiders  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 18-June 10

Posted 18 June 2010 - 07:09 AM

How did you get around the Primary id problem when adding a new record ?...youare giving it a "text"...should it not be numeric, or integer ?

I can't find the attachment example either ?


Thanks
fordraiders
Was This Post Helpful? 0
  • +
  • -

#10 philip_john  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 04-February 11

Posted 07 February 2011 - 03:47 AM

Thank for this tutorial. This code really help me on my system. Just remember the CRUD(Create, Read, Update, Delete) in the database plus this code equals cool system.... :D
Was This Post Helpful? 0
  • +
  • -

#11 Inope  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 07-February 11

Posted 09 February 2011 - 01:06 AM

The code is duplicating my table on the data grid making it two, what can i do please...thanks
Was This Post Helpful? 0
  • +
  • -

#12 luchador  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 3
  • Joined: 07-March 11

Posted 07 March 2011 - 07:12 AM

thank you for this tuts ;)
Was This Post Helpful? 1
  • +
  • -

#13 mana973  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 04-October 11

Posted 04 October 2011 - 02:35 AM

thank you for this tuts
Was This Post Helpful? 0
  • +
  • -

#14 mvdsa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 29-November 11

Posted 29 November 2011 - 06:53 AM

HI....
Your code was realllyyy cool Thank you..

One question Im really stuck with:
How do i update data at Cellendedit

so I want to enter data in datagridview and then when I move down it will auto update table.

I can do it with Bound data but not this way..

Any ideas..

Hope this makes sense..

Thanks man
MJ
Was This Post Helpful? 0
  • +
  • -

#15 Jerrrum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 08-February 12

Posted 09 February 2012 - 11:07 AM

This is exactly what i needed to get started! Thanks! :^:
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2