by SGL (1 Submission)
Category: Miscellaneous
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Wed 25th August 2004
Date Added: Mon 8th February 2021
Rating: (1 Votes)
Data Access using ADO.NET
API Declarations
Imports Microsoft.VisualBasic
Public Class Form1
Inherits System.Windows.Forms.Form
Dim ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\add_book.mdb;"
Dim conn As New OleDbConnection(ConnStr)
Dim cmd As New OleDbCommand("select * from contact", conn)
Dim da As New OleDbDataAdapter(cmd)
Dim dSet As New DataSet()
Dim i As Int16
Dim curMode As String
Dim sql As String
i = 0
dSet = New DataSet("contact")
Try
da.Fill(dSet, "contact")
'MsgBox(dSet.Tables("contact").Rows.Count)
txtname.Text = dSet.Tables("contact").Rows(i).Item(0)
txtcity.Text = dSet.Tables("contact").Rows(i).Item(1)
txtemail.Text = dSet.Tables("contact").Rows(i).Item(2)
Catch ex As OleDbException
MsgBox(ex.Message)
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
curMode = "Add"
txtname.Text = ""
txtcity.Text = ""
txtemail.Text = ""
Button1.Enabled = False
Button2.Enabled = True
Button3.Enabled = False
Button4.Enabled = False
txtname.Focus()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim msg As String
If curMode = "Add" Then
sql = "INSERT INTO contact(name,city,email) VALUES('" & UCase(txtname.Text) & " ','" & UCase(txtcity.Text) & " ','" & txtemail.Text & " ')"
msg = "Record added successfully"
ElseIf curMode = "Delete" Then
sql = "DELETE FROM contact WHERE name='" & txtname.Text & " ' "
msg = "Record deleted"
i = i + 1
ElseIf curMode = "Edit" Then
sql = "UPDATE contact SET name='" & txtname.Text & " ',city='" & txtcity.Text & " ',email='" & txtemail.Text & " ' WHERE name='" & txtname.Text & " ' OR city='" & txtcity.Text & " ' OR email='" & txtemail.Text & " ' "
msg = "Record updated"
End If
cmd = New OleDbCommand(sql, conn)
da = New OleDbDataAdapter(cmd)
Try
da.Fill(dSet, "contact")
MsgBox(msg, MsgBoxStyle.Information)
DataGrid1.Refresh()
Button1.Enabled = True
Button3.Enabled = True
Button4.Enabled = True
Button2.Enabled = False
'dSet.Tables("contact").DataSet.Reset()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Information)
End Try
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
i = 0
txtname.Text = dSet.Tables("contact").Rows(i).Item(0)
txtcity.Text = dSet.Tables("contact").Rows(i).Item(1)
txtemail.Text = dSet.Tables("contact").Rows(i).Item(2)
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
i = dSet.Tables("contact").Rows.Count - 1
txtname.Text = dSet.Tables("contact").Rows(i).Item(0)
txtcity.Text = dSet.Tables("contact").Rows(i).Item(1)
txtemail.Text = dSet.Tables("contact").Rows(i).Item(2)
End Sub
Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
i = i + 1
If i > dSet.Tables("contact").Rows.Count - 1 Then
MsgBox("This is Last Record", MsgBoxStyle.Information)
i = dSet.Tables("contact").Rows.Count - 1
Else
txtname.Text = dSet.Tables("contact").Rows(i).Item(0)
txtcity.Text = dSet.Tables("contact").Rows(i).Item(1)
txtemail.Text = dSet.Tables("contact").Rows(i).Item(2)
End If
End Sub
Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
i = i - 1
If i < 0 Then
MsgBox("This is First Record", MsgBoxStyle.Information)
i = 0
Else
txtname.Text = dSet.Tables("contact").Rows(i).Item(0)
txtcity.Text = dSet.Tables("contact").Rows(i).Item(1)
txtemail.Text = dSet.Tables("contact").Rows(i).Item(2)
End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
curMode = "Delete"
Button1.Enabled = False
Button2.Enabled = True
Button3.Enabled = False
Button4.Enabled = False
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
curMode = "Edit"
Button1.Enabled = False
Button2.Enabled = True
Button3.Enabled = False
Button4.Enabled = False
End Sub
Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
DataGrid1.DataSource = dSet.Tables("contact")
End Sub