Imports System.Data
Imports System.Data.SqlClient
Public Class Form7
Dim con As SqlConnection
Dim da As SqlDataAdapter
Dim ds As New DataSet
Dim cb As SqlCommandBuilder
Dim i As Integer = 0
Private Sub Form7_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
con = New SqlConnection(“data source=sankar\sqlexpress;initial catalog=mydatabase;integrated security=true;”)
da = New SqlDataAdapter(“select * from emp”, con)
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
cb = New SqlCommandBuilder(da)
da.Fill(ds, “emp”)
FillMe(i)
End Sub
Sub FillMe(ByVal i As Integer)
TextBox1.Text = ds.Tables(“emp”).Rows(i)(0)
TextBox2.Text = ds.Tables(“emp”).Rows(i)(1)
TextBox3.Text = ds.Tables(“emp”).Rows(i)(2)
TextBox4.Text = ds.Tables(“emp”).Rows(i)(3)
TextBox5.Text = ds.Tables(“emp”).Rows(i)(4)
Label6.Text = i + 1 & “ of “ & ds.Tables(“emp”).Rows.Count
End Sub
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
i = 0
FillMe(i)
End Sub
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
i = ds.Tables(“emp”).Rows.Count – 1
FillMe(i)
End Sub
Private Sub btnPre_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPre.Click
If i = 0 Then
MsgBox(“You are now Seeing First record”)
Exit Sub
Else
i -= 1
End If
FillMe(i)
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If i = ds.Tables(“emp”).Rows.Count – 1 Then
MsgBox(“You are in last record”)
Exit Sub
Else
i += 1
End If
FillMe(i)
End Sub
Private Sub LinkLabel1_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
Me.Close()
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
TextBox1.Text = “”
TextBox2.Text = “”
TextBox3.Text = “”
TextBox4.Text = “”
TextBox5.Text = “”
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim r As DataRow
r = ds.Tables(“emp”).NewRow
r(0) = CInt(TextBox1.Text)
r(1) = TextBox2.Text
r(2) = TextBox3.Text
r(3) = CDbl(TextBox4.Text)
r(4) = CInt(TextBox5.Text)
ds.Tables(“emp”).Rows.Add(r)
Try
da.Update(ds, “emp”)
MsgBox(“one row added successfully”)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Button5_Click(sender, e)
FillMe(ds.Tables(“emp”).Rows.Count – 1)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
ds.Tables(“emp”).Rows(i)(1) = TextBox2.Text
ds.Tables(“emp”).Rows(i)(2) = TextBox3.Text
ds.Tables(“emp”).Rows(i)(3) = CDbl(TextBox4.Text)
ds.Tables(“emp”).Rows(i)(4) = CInt(TextBox5.Text)
Try
da.Update(ds, “emp”)
MsgBox(“One row updated successfully”)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
ds.Tables(“emp”).Rows(i).Delete()
Try
da.Update(ds, “emp”)
MsgBox(“one row deleted sucessfully”)
btnFirst_Click(sender, e)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim r As DataRow
r = ds.Tables(“emp”).Rows.Find(CInt(TextBox1.Text))
If Not r Is Nothing Then
TextBox2.Text = r(1)
TextBox3.Text = r(2)
TextBox4.Text = r(3)
TextBox5.Text = r(4)
Else
MsgBox(“NO matching records”)
End If
End Sub
End Class
–
Place DataGridView on form:
Imports System.Data.SqlClient
Public Class Form9
Private Sub Form9_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim con As New SqlConnection(“data source=san\sqlexpress;initial catalog=northwind;integrated security=true;”)
Using (con)
Dim da As New SqlDataAdapter(“select employeeid,firstname,lastname,photo from employees”, con)
Dim ds As New DataSet
da.Fill(ds, “employees”)
DataGridView1.AutoGenerateColumns = False
DataGridView1.RowTemplate.Height = 100
DataGridView1.DataSource = ds.Tables(“employees”)
setUpcolumns(ds)
End Using
End Sub
Sub setUpcolumns(ByVal ds As DataSet)
Dim id As New DataGridViewTextBoxColumn
id.DataPropertyName = “EmployeeId”
id.HeaderText = “Employee NO”
Dim f As New DataGridViewTextBoxColumn
f.DataPropertyName = “firstname”
f.HeaderText = “First Name”
Dim l As New DataGridViewTextBoxColumn
l.HeaderText = “Last Name”
l.DataPropertyName = “lastname”
Dim p As New DataGridViewImageColumn
p.ReadOnly = True
p.ImageLayout = DataGridViewImageCellLayout.Stretch
p.Width = 100
p.DataPropertyName = “photo”
p.HeaderText = “Photo”
With DataGridView1.Columns
.Add(id)
.Add(f)
.Add(l)
.Add(p)
End With
End Sub
End Class
