Connection To Access Database and Save,Retrieve,Update
D:\access\try.mdb=The Address of the Access database table 'Try'.
Take a module as ModMain(Copy the code)
table fields: idno,name,designation.(idno=auto number)
textBoxes=TxtName,TxtDesignation And Combobox=CboRetrieve
buttons=new,save,retrieve,exit
OOLEDB Connection(vb.net & Access)
Module ModMain
Public Const OLEDB_CONNECTION_STRING As String = _
"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Database Password=soumya;Data Source=D:\access\try.mdb;Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"
'"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=0;Data Source=D:\access\try.mdb ;Mode=Share Deny None;Jet OLEDB:Engine Type=5;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"
'"workstation id=USER21;packet size=4096;integrated security=SSPI;data source=USER12;persist security info=False;initial catalog=Northwind"
Public Const ConnectionString As String = OLEDB_CONNECTION_STRING
Public mode As Boolean
Public DSTry As New DataSet
'Public DSTry As New System.Data.DataSet
Public Const Select_Command As String = "select idno,name,designation from try ORDER BY IDNO"
End Module
#Region "General Declarations"
Option Explicit On
'Option Strict On
Imports System.Data
Imports System.Data.OleDb
#End Region
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
'the follwing matter within brace will be automatically generated
[
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents TxtIDno As System.Windows.Forms.TextBox
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents BtnNew As System.Windows.Forms.Button
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents BtnSave As System.Windows.Forms.Button
Friend WithEvents BtnRetrieve As System.Windows.Forms.Button
Friend WithEvents BtnExit As System.Windows.Forms.Button
Friend WithEvents CboRetrieve As System.Windows.Forms.ComboBox
Friend WithEvents TxtName As System.Windows.Forms.TextBox
Friend WithEvents TxtDesignation As System.Windows.Forms.TextBox
Friend WithEvents LinkLabel1 As System.Windows.Forms.LinkLabel
Friend WithEvents Label4 As System.Windows.Forms.Label
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.TxtIDno = New System.Windows.Forms.TextBox
Me.Label1 = New System.Windows.Forms.Label
Me.TxtName = New System.Windows.Forms.TextBox
Me.Label2 = New System.Windows.Forms.Label
Me.Label3 = New System.Windows.Forms.Label
Me.TxtDesignation = New System.Windows.Forms.TextBox
Me.BtnNew = New System.Windows.Forms.Button
Me.GroupBox1 = New System.Windows.Forms.GroupBox
Me.BtnExit = New System.Windows.Forms.Button
Me.BtnRetrieve = New System.Windows.Forms.Button
Me.BtnSave = New System.Windows.Forms.Button
Me.CboRetrieve = New System.Windows.Forms.ComboBox
Me.LinkLabel1 = New System.Windows.Forms.LinkLabel
Me.Label4 = New System.Windows.Forms.Label
Me.GroupBox1.SuspendLayout()
Me.SuspendLayout()
'
'TxtIDno
'
Me.TxtIDno.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
Me.TxtIDno.Location = New System.Drawing.Point(112, 32)
Me.TxtIDno.Name = "TxtIDno"
Me.TxtIDno.Size = New System.Drawing.Size(64, 20)
Me.TxtIDno.TabIndex = 0
Me.TxtIDno.Text = ""
'
'Label1
'
Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Label1.ForeColor = System.Drawing.SystemColors.HotTrack
Me.Label1.Location = New System.Drawing.Point(56, 32)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(48, 16)
Me.Label1.TabIndex = 1
Me.Label1.Text = "ID No"
'
'TxtName
'
Me.TxtName.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
Me.TxtName.Font = New System.Drawing.Font("Times New Roman", 9.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.TxtName.ForeColor = System.Drawing.Color.Black
Me.TxtName.Location = New System.Drawing.Point(112, 72)
Me.TxtName.Name = "TxtName"
Me.TxtName.Size = New System.Drawing.Size(144, 22)
Me.TxtName.TabIndex = 2
Me.TxtName.Text = ""
'
'Label2
'
Me.Label2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Label2.Location = New System.Drawing.Point(56, 72)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(48, 16)
Me.Label2.TabIndex = 3
Me.Label2.Text = "Name"
'
'Label3
'
Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Label3.Location = New System.Drawing.Point(32, 112)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(72, 16)
Me.Label3.TabIndex = 4
Me.Label3.Text = "Designation"
'
'TxtDesignation
'
Me.TxtDesignation.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
Me.TxtDesignation.Font = New System.Drawing.Font("Times New Roman", 9.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.TxtDesignation.ForeColor = System.Drawing.Color.Black
Me.TxtDesignation.Location = New System.Drawing.Point(112, 112)
Me.TxtDesignation.Name = "TxtDesignation"
Me.TxtDesignation.Size = New System.Drawing.Size(144, 22)
Me.TxtDesignation.TabIndex = 5
Me.TxtDesignation.Text = ""
'
'BtnNew
'
Me.BtnNew.BackColor = System.Drawing.Color.Gainsboro
Me.BtnNew.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.BtnNew.ForeColor = System.Drawing.SystemColors.HotTrack
Me.BtnNew.Location = New System.Drawing.Point(8, 24)
Me.BtnNew.Name = "BtnNew"
Me.BtnNew.Size = New System.Drawing.Size(64, 32)
Me.BtnNew.TabIndex = 6
Me.BtnNew.Text = "New"
'
'GroupBox1
'
Me.GroupBox1.Controls.Add(Me.BtnExit)
Me.GroupBox1.Controls.Add(Me.BtnRetrieve)
Me.GroupBox1.Controls.Add(Me.BtnSave)
Me.GroupBox1.Controls.Add(Me.BtnNew)
Me.GroupBox1.Location = New System.Drawing.Point(8, 160)
Me.GroupBox1.Name = "GroupBox1"
Me.GroupBox1.Size = New System.Drawing.Size(280, 72)
Me.GroupBox1.TabIndex = 7
Me.GroupBox1.TabStop = False
'
'BtnExit
'
Me.BtnExit.BackColor = System.Drawing.Color.Gainsboro
Me.BtnExit.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.BtnExit.Location = New System.Drawing.Point(206, 24)
Me.BtnExit.Name = "BtnExit"
Me.BtnExit.Size = New System.Drawing.Size(64, 32)
Me.BtnExit.TabIndex = 9
Me.BtnExit.Text = "Exit"
'
'BtnRetrieve
'
Me.BtnRetrieve.BackColor = System.Drawing.Color.Gainsboro
Me.BtnRetrieve.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.BtnRetrieve.Location = New System.Drawing.Point(140, 24)
Me.BtnRetrieve.Name = "BtnRetrieve"
Me.BtnRetrieve.Size = New System.Drawing.Size(64, 32)
Me.BtnRetrieve.TabIndex = 8
Me.BtnRetrieve.Text = "Retrieve"
'
'BtnSave
'
Me.BtnSave.BackColor = System.Drawing.Color.Gainsboro
Me.BtnSave.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.BtnSave.ForeColor = System.Drawing.SystemColors.HotTrack
Me.BtnSave.Location = New System.Drawing.Point(74, 24)
Me.BtnSave.Name = "BtnSave"
Me.BtnSave.Size = New System.Drawing.Size(64, 32)
Me.BtnSave.TabIndex = 7
Me.BtnSave.Text = "Save"
'
'CboRetrieve
'
Me.CboRetrieve.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
Me.CboRetrieve.Location = New System.Drawing.Point(112, 32)
Me.CboRetrieve.Name = "CboRetrieve"
Me.CboRetrieve.Size = New System.Drawing.Size(64, 21)
Me.CboRetrieve.TabIndex = 8
'
'LinkLabel1
'
Me.LinkLabel1.FlatStyle = System.Windows.Forms.FlatStyle.Popup
Me.LinkLabel1.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.LinkLabel1.Location = New System.Drawing.Point(96, 240)
Me.LinkLabel1.Name = "LinkLabel1"
Me.LinkLabel1.Size = New System.Drawing.Size(112, 16)
Me.LinkLabel1.TabIndex = 9
Me.LinkLabel1.TabStop = True
Me.LinkLabel1.Text = "Data Grid View"
'
'Label4
'
Me.Label4.Location = New System.Drawing.Point(8, 232)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(280, 32)
Me.Label4.TabIndex = 10
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.BackColor = System.Drawing.SystemColors.ActiveBorder
Me.ClientSize = New System.Drawing.Size(288, 261)
Me.Controls.Add(Me.LinkLabel1)
Me.Controls.Add(Me.CboRetrieve)
Me.Controls.Add(Me.TxtIDno)
Me.Controls.Add(Me.TxtDesignation)
Me.Controls.Add(Me.TxtName)
Me.Controls.Add(Me.GroupBox1)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.Label4)
Me.ForeColor = System.Drawing.SystemColors.HotTrack
Me.Name = "Form1"
Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
Me.Text = "Form1"
Me.GroupBox1.ResumeLayout(False)
Me.ResumeLayout(False)
End Sub
]
#End Region
#Region "Load Events"
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
CboRetrieve.Visible = False
CboRetrieve.Enabled = False
TxtIDno.Enabled = False
mode = True
End Sub
#End Region
#Region "Procedural Declarations"
Private Sub Count()
Dim cnSQL As OleDbConnection
Dim cmSQL As OleDbCommand
Dim drSQL As OleDbDataReader
Dim strSQL As String
Try
strSQL = "select count(*) from try"
cnSQL = New OleDbConnection(ConnectionString)
cnSQL.Open()
cmSQL = New OleDbCommand(strSQL, cnSQL)
drSQL = cmSQL.ExecuteReader()
If drSQL.Read() Then
TxtIDno.Text = drSQL.Item("idno").ToString()
End If
drSQL.Close()
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
Catch e As OleDbException
MsgBox(e.Message, MsgBoxStyle.Critical, "oledb Error")
Catch e As Exception
MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub
Private Sub clearCombo()
CboRetrieve.SelectedIndex = -1
CboRetrieve.SelectedItem = 0
CboRetrieve.SelectedValue = 0
CboRetrieve.SelectionLength = 0
End Sub
Private Sub combo()
'Dim strSql As String = "SELECT * FROM try order by idno"
Dim adap As New OleDb.OleDbDataAdapter(Select_Command, ConnectionString)
adap.Fill(DSTry, "try")
adap.Dispose()
CboRetrieve.DataSource = DSTry.Tables("try")
CboRetrieve.DisplayMember = DSTry.Tables("try").Columns(0).ColumnName
CboRetrieve.ValueMember = DSTry.Tables("try").Columns(0).ColumnName
End Sub
Private Sub Save_me()
Dim DAdapter As New OleDb.OleDbDataAdapter(Select_Command, ConnectionString)
Try
Me.BindingContext(DSTry, _
"try").EndCurrentEdit()
DAdapter.Update(DSTry, _
"try")
MessageBox.Show("Saved Successfully")
Catch eSave As System.Exception
MessageBox.Show(eSave.Message)
End Try
End Sub
'Dim strSql As String = "insert into try(name,designation) values"
Private Sub CheckEntry()
'Dim cnSQL As OleDbConnection
'Dim cmSQL As OleDbCommand
'Dim strSQL As String
'Dim verSqlRed As OleDbDataReader
'Try
'strSQL = "select * from try" & _
'" where idno = " & TxtIDno.Text & ""
'cnSQL = New OleDbConnection(ConnectionString)
'cnSQL.Open()
'cmSQL = New OleDbCommand(strSQL, cnSQL)
'verSqlRed = cmSQL.ExecuteReader
'If verSqlRed.Read = True Then
' MessageBox.Show("This ID Number Already Exists")
' TxtIDno.Text = ""
' TxtIDno.Focus()
' Exit Sub
If mode = True Then
' Dim msg As String
' Dim title As String
' Dim style As MsgBoxStyle
' Dim response As MsgBoxResult
' msg = "The Mobile Number Already Exists,Do you want to update the data?"
' style = MsgBoxStyle.DefaultButton2 Or _
' MsgBoxStyle.Critical Or MsgBoxStyle.YesNo
' title = "Confirmation of Saving data" ' Define title.
' response = MsgBox(msg, style, title)
' If response = MsgBoxResult.No Then
' Exit Sub
' Else
' Call updatedata()
' End If
'Else
Call SaveData()
'''Call Save_me()
Else
Call updatedata()
'''Call Save_me()
End If
' cnSQL.Close()
' cmSQL.Dispose()
' cnSQL.Dispose()
'Catch e As OleDbException
' MsgBox(e.Message, MsgBoxStyle.Critical, "Oledb Error")
'Catch e As Exception
' MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
'End Try
End Sub
Private Sub SaveData()
If TxtName.Text = "" Or TxtDesignation.Text = "" Then
MessageBox.Show("Fill Data into Every Field")
Exit Sub
Else
Dim msg As String
Dim title As String
Dim style As MsgBoxStyle
Dim response As MsgBoxResult
msg = "Do you want to save the data?"
style = MsgBoxStyle.DefaultButton2 Or _
MsgBoxStyle.Critical Or MsgBoxStyle.YesNo
title = "Confirmation of Saving data" ' Define title.
response = MsgBox(msg, style, title)
If response = MsgBoxResult.No Then
Exit Sub
Else
Dim cnSQL As OleDbConnection
Dim cmSQL As OleDbCommand
Dim strSQL As String
Dim intRowsAffected As Integer
Try
strSQL = "insert into try(name,designation)" & _
" values('" & _
TxtName.Text & "', '" & _
TxtDesignation.Text & "')"
cnSQL = New OleDbConnection(ConnectionString)
cnSQL.Open()
cmSQL = New OleDbCommand(strSQL, cnSQL)
cmSQL.ExecuteNonQuery()
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
MessageBox.Show("Saved Successfully")
Call clear()
Catch Exp As OleDbException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "Oledb Error")
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try
End If
End If
End Sub
Private Sub clear()
'TxtIDno.Text = ""
TxtIDno.Visible = True
TxtIDno.Enabled = False
TxtName.Text = ""
TxtDesignation.Text = ""
End Sub
Private Sub updatedata()
If TxtName.Text = "" Or TxtDesignation.Text = "" Then
MessageBox.Show("Fill Data into Every Field")
Exit Sub
Else
Dim cnSQL As OleDbConnection
Dim cmSQL As OleDbCommand
Dim strSQL As String
Dim intRowsAffected As Integer
Try
strSQL = "UPDATE try SET" & _
" Name = '" & TxtName.Text & "'" & _
" ,designation = '" & TxtDesignation.Text & "'" & _
" WHERE idno = " & CboRetrieve.Text & ""
cnSQL = New OleDbConnection(ConnectionString)
cnSQL.Open()
cmSQL = New OleDbCommand(strSQL, cnSQL)
intRowsAffected = cmSQL.ExecuteNonQuery()
If intRowsAffected <> 1 Then
MsgBox("Update Failed.", MsgBoxStyle.Critical, "Update")
End If
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
MessageBox.Show("Updated Successfully")
Catch e As OleDbException
MsgBox(e.Message, MsgBoxStyle.Critical, "Oledb Error")
Catch e As Exception
MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
End Try
End If
clear()
clearCombo()
End Sub
Private Sub PopulateForm()
Dim cnSQL As OleDbConnection
Dim cmSQL As OleDbCommand
Dim drSQL As OleDbDataReader
Dim strSQL As String
Try
strSQL = "SELECT name, " & _
"designation " & _
"FROM try " & _
"WHERE idno = " & CboRetrieve.Text & ""
cnSQL = New OleDbConnection(ConnectionString)
cnSQL.Open()
cmSQL = New OleDbCommand(strSQL, cnSQL)
drSQL = cmSQL.ExecuteReader()
If drSQL.Read() Then
TxtName.Text = drSQL.Item("NAME").ToString()
TxtDesignation.Text() = drSQL.Item("designation").ToString()
End If
drSQL.Close()
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
Catch e As OleDbException
MsgBox(e.Message, MsgBoxStyle.Critical, "oledb Error")
Catch e As Exception
MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub
#End Region
#Region "Click Events"
Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
'Call CheckEntry()
Call Save_me()
'Dim DAdapter As New OleDb.OleDbDataAdapter(Select_Command, ConnectionString)
'Try
' Me.BindingContext(DSTry, _
' "Try").EndCurrentEdit()
' DAdapter.Update(DSTry, _
' "Try")
' MessageBox.Show("Saved Successfully")
'Catch eSave As System.Exception
' MessageBox.Show(eSave.Message)
'End Try
End Sub
Private Sub BtnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNew.Click
mode = True
CboRetrieve.Visible = False
CboRetrieve.Enabled = False
TxtIDno.Visible = True
TxtIDno.Enabled = True
TxtIDno.Focus()
Call clear()
'Call Count()
End Sub
Private Sub BtnRetrieve_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnRetrieve.Click
mode = False
CboRetrieve.Visible = True
TxtIDno.Visible = False
CboRetrieve.Enabled = True
TxtIDno.Enabled = False
Call combo()
Call clearCombo()
End Sub
Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
End
'Me.Hide()
End Sub
Private Sub CboRetrieve_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles CboRetrieve.SelectionChangeCommitted
PopulateForm()
End Sub
#End Region
Private Sub LinkLabel1_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
Dim form1 As New FrmGrid
form1.Show()
Call clearCombo()
Call clear()
End Sub
End Class
bumsfeld 413 Nearly a Posting Virtuoso
decklon 0 Newbie Poster
elone25 0 Newbie Poster
bruce2424 0 Newbie Poster
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.