Hi,
I have my database in SQL.
I want to navigate the records on my form in vb.net NEXT, PREVIOUS etc.
I want a sample code to have a look so that it would help me as a guideline.
Thanks!
Printable View
Hi,
I have my database in SQL.
I want to navigate the records on my form in vb.net NEXT, PREVIOUS etc.
I want a sample code to have a look so that it would help me as a guideline.
Thanks!
HI,
Please go through this tutorial I think this will make you understand the way next previous buttons work so that you can navigate the records in textboxes on your Vb.net form with SQL as your database!
http://www.homeandlearn.co.uk/NET/nets12p7.html
and
http://www.vbdotnetheaven.com/Upload...avigation.aspx
Hope this helps!
This code shows how to implement navigation records using VB.NET
Code:Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class Frmmain
Dim constr As New OleDbConnection
Dim da As OleDbDataAdapter
Dim dS As New DataSet
Dim a As Integer = 0
Dim dBind As New BindingSource
Private Sub Frmmain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Call md_settings()
Call md_fillevents()
End Sub
Private Sub md_settings()
Try
Dim ConnString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN")
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = ConnString
myConnection.Open()
Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from emp_mstr", ConnString)
dS = New DataSet
da.Fill(dS, "emp")
dBind.DataSource = dS
DataGridView1.DataSource = dS
dBind.DataMember = dS.Tables(0).ToString()
DataGridView1.DataSource = dBind
Catch ex As Exception
End Try
End Sub
Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
Try
Dim ConnString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN")
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = ConnString
Dim dsinvoice As New DataSet
Dim dainvoice As New OleDbDataAdapter
Dim sql As String = "insert into emp_mstr(emp_no, fname, mname, lname, dept_no, desig) values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "')"
myConnection.Open()
Dim com As New OleDbCommand
Dim dbread
com = New OleDbCommand(sql, myConnection)
dbread = com.ExecuteReader()
'com.ExecuteNonQuery()
MsgBox("Records Inserted Successfully...")
Call md_settings()
Catch
MsgBox("Records not Inserted...")
End Try
End Sub
Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
If Not IsDBNull(DataGridView1.CurrentRow.Cells(0).Value) Then
TextBox1.Text = DataGridView1.CurrentRow.Cells(0).Value
Else
TextBox1.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(1).Value) Then
TextBox2.Text = DataGridView1.CurrentRow.Cells(1).Value
Else
TextBox2.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(2).Value) Then
TextBox3.Text = DataGridView1.CurrentRow.Cells(2).Value
Else
TextBox3.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(3).Value) Then
TextBox4.Text = DataGridView1.CurrentRow.Cells(3).Value
Else
TextBox4.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(4).Value) Then
TextBox5.Text = DataGridView1.CurrentRow.Cells(4).Value
Else
TextBox5.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(5).Value) Then
TextBox6.Text = DataGridView1.CurrentRow.Cells(5).Value
Else
TextBox6.Text = ""
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Dim ConnString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN")
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = ConnString
Dim dsinvoice As New DataSet
Dim dainvoice As New OleDbDataAdapter
Dim sql As String = "update emp_mstr set emp_no='" & TextBox1.Text & "',fname='" & TextBox2.Text & "',mname='" & TextBox3.Text & "',lname='" & TextBox4.Text & "',dept_no='" & TextBox5.Text & "',desig='" & TextBox6.Text & "' WHERE EMP_NO='" & TextBox1.Text & "'"
myConnection.Open()
Dim com As New OleDbCommand
Dim dbread
com = New OleDbCommand(sql, myConnection)
dbread = com.ExecuteReader()
MsgBox("Records Updated")
Call md_settings()
Catch
End Try
End Sub
Private Sub md_fillevents()
Try
TextBox1.Text = dS.Tables(0).Rows(a).Item(0)
TextBox2.Text = dS.Tables(0).Rows(a).Item(1)
TextBox3.Text = dS.Tables(0).Rows(a).Item(2)
TextBox4.Text = dS.Tables(0).Rows(a).Item(3)
TextBox5.Text = dS.Tables(0).Rows(a).Item(4)
TextBox6.Text = dS.Tables(0).Rows(a).Item(5)
Catch ex As Exception
MsgBox(ex.ToString())
End Try
End Sub
Private Sub cmdfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdfirst.Click
Try
a = 0
Call md_fillevents()
Catch ex As Exception
End Try
End Sub
Private Sub cmdprior_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdprior.Click
Try
If (Not a = 0) Then
a -= 1
Call md_fillevents()
End If
Catch ex As Exception
End Try
End Sub
Private Sub cmdnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdnext.Click
Try
If (Not a = dS.Tables(0).Rows.Count - 1) Then
a += 1
Call md_fillevents()
End If
Catch ex As Exception
End Try
End Sub
Private Sub cmdlast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdlast.Click
Try
If (Not a = dS.Tables(0).Rows.Count - 1) Then
a = dS.Tables(0).Rows.Count - 1
Call md_fillevents()
End If
Catch ex As Exception
End Try
End Sub
Private Sub DataGridView1_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
If Not IsDBNull(DataGridView1.CurrentRow.Cells(0).Value) Then
TextBox1.Text = DataGridView1.CurrentRow.Cells(0).Value
Else
TextBox1.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(1).Value) Then
TextBox2.Text = DataGridView1.CurrentRow.Cells(1).Value
Else
TextBox2.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(2).Value) Then
TextBox3.Text = DataGridView1.CurrentRow.Cells(2).Value
Else
TextBox3.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(3).Value) Then
TextBox4.Text = DataGridView1.CurrentRow.Cells(3).Value
Else
TextBox4.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(4).Value) Then
TextBox5.Text = DataGridView1.CurrentRow.Cells(4).Value
Else
TextBox5.Text = ""
End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(5).Value) Then
TextBox6.Text = DataGridView1.CurrentRow.Cells(5).Value
Else
TextBox6.Text = ""
End If
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""
Me.TextBox4.Text = ""
Me.TextBox5.Text = ""
Me.TextBox6.Text = ""
Me.TextBox1.Focus()
End Sub
End Class
The position of a Table in a DataSet is controlled by a binding context. You can use the form's BindingContext method to get the binding context associated with a DataSet and table. This object's Position property determines its position within the table.
To move to the first record, set Position = 0. To move to the previous or next record, subtract or add 1 to Position. To move to the last record, use the binding context's Count property get the number of records and set Position equal the number of records minus 1.
To add a record, call the binding context's AddNew method. To remove a record, make the user connfirm the deletion and then calls the binding context's RemoveAt method, passing it the index of the record to remove.
' Move to the previous record in the dsUsers
' DataSet's People table.
Code:Private Sub btnPrev_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnPrev.Click
Me.BindingContext(dsUsers, "People").Position -= 1
End Sub
' Move to the next record in the dsUsers
' DataSet's People table.
Private Sub btnNext_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnNext.Click
Me.BindingContext(dsUsers, "People").Position += 1
End Sub
' Move to the last record in the dsUsers
' DataSet's People table.
Private Sub btnLast_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnLast.Click
Me.BindingContext(dsUsers, "People").Position = _
Me.BindingContext(dsUsers, "People").Count - 1
End Sub
' Add a new record.
Private Sub btnAdd_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnAdd.Click
Me.BindingContext(dsUsers, "People").AddNew()
End Sub
' Delete the current record.
Private Sub btnDelete_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnDelete.Click
If MsgBox("Are you sure you want to delete the record " & _
"for " & _
txtFirstName.Text & " " & txtLastName.Text & "?", _
MsgBoxStyle.Question Or MsgBoxStyle.YesNo, _
"Confirm") = MsgBoxResult.Yes _
Then
Me.BindingContext(dsUsers, "People"). _
RemoveAt(Me.BindingContext(dsUsers, _
"People").Position)
End If
End Sub
First, add databindings to the text boxes:
To do the previous and next buttons use this code (I also threw in Last and First record code):Code:txtMI.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSetName, "MI"))
Code:'Next:
Me.BindingContext(DataSetName, "TableName").Position = _
(Me.BindingContext(DataSetName, "TableName").Position + 1)
'Previous:
Me.BindingContext(DataSetName, "TableName").Position = _
(Me.BindingContext(DataSetName, "TableName").Position - 1)
'Last:
Me.BindingContext(DataSetName, "TableName").Position = _
(Me.objdsDataForm.Tables("TableName").Rows.Count - 1)
'First:
Me.BindingContext(objdsDataForm, "TableName").Position = 0