Getting data to scroll through a Datagridview and Textboxes Sequentially

Question

I am getting data from an SQL Server Database to display into text boxes.

However I also tried getting individual data or current data to display in the Gridview but not working. I am sure I am not doing it right.

I can get the data to display in the Grid view but not individually or scroll through as I hit the next button.

I want the SALES_REP data to also display in the Gridview individually as I use the buttons to browse through.

Any ideas where I am going wrong?

Here is my code:

Imports System.Data
Imports System.Data.SqlClient

Public Class frmBindingData

    Dim objConnection As New SqlConnection _
        ("server=ROCKET;Database=rocketship;Trusted_Connection=True;")
    Dim objDataAdapter As New SqlDataAdapter("EXEC spSALES_REP", objConnection)
    Dim objDataSet As DataSet

    Dim objDataView As DataView
    Dim objCurrencyManager As CurrencyManager


    Private Sub FillDataSetAndView()
        ' Initialize a new instance of the DataSet object..
        objDataSet = New DataSet()
        ' Fill the DataSet object with data..
        objDataAdapter.Fill(objDataSet, "EXEC spSALES_REP")
        ' Set the DataView object to the DataSet object..

        objDataView = New DataView(objDataSet.Tables("EXEC spSALES_REP"), "", "REP_ID", DataViewRowState.CurrentRows)
        'objDataView = New DataView(objDataSet.Tables("SALES_REP"))
        ' Set our CurrencyManager object to the DataView object..
        objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)
    End Sub


    Private Sub BindFields()
        ' Clear any previous bindings..
        txtIDNumber.DataBindings.Clear()
        txtFirstName.DataBindings.Clear()
        txtLastName.DataBindings.Clear()
        txtDateHire.DataBindings.Clear()
        txtCommision.DataBindings.Clear()

        ' Add new bindings to the DataView object.
        txtIDNumber.DataBindings.Add("Text", objDataView, "REP_ID")
        txtFirstName.DataBindings.Add("Text", objDataView, "FIRST_NAME")
        txtLastName.DataBindings.Add("Text", objDataView, "LAST_NAME")
        txtDateHire.DataBindings.Add("text", objDataView, "HIRE_DATE")
        txtCommision.DataBindings.Add("Text", objDataView, "COMMISION")

        ' Display a ready status..
        ToolStripStatusLabel1.Text = "Ready"
    End Sub


    Private Sub ShowPosition()
        'Always format the number in the txtPrice field to include cents
        Try
            txtCommision.Text = Format(CType(txtCommision.Text, Decimal), "c")
        Catch e As System.Exception
            txtCommision.Text = "0"
            txtCommision.Text = Format(CType(txtCommision.Text, Decimal), "c")
        End Try
        ' Display the current position and the number of records
        txtRecordPosition.Text = objCurrencyManager.Position + 1 & _
        " of " & objCurrencyManager.Count()
    End Sub


    Private Sub grpDataDisplay_Enter(sender As System.Object, e As System.EventArgs) Handles grpDataDisplay.Enter

    End Sub

    Private Sub StatusStrip1_ItemClicked(sender As System.Object, e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles StatusStrip1.ItemClicked

    End Sub

    Private Sub frmBindingData_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ' Fill the DataSet and bind the fields..
        FillDataSetAndView()
        BindFields()
        ' Show the current record position..
        ShowPosition()

    End Sub

    Private Sub btnMoveFirst_Click(sender As System.Object, e As System.EventArgs) Handles btnMoveFirst.Click
        ' Set the record position to the first record..
        objCurrencyManager.Position = 0
        ' Show the current record position..
        ShowPosition()
    End Sub

    Private Sub btnMovePrevious_Click(sender As System.Object, e As System.EventArgs) Handles btnMovePrevious.Click
        ' Move to the previous record..
        objCurrencyManager.Position -= 1
        ' Show the current record position..
        ShowPosition()

    End Sub

    Private Sub btnMoveNext_Click(sender As System.Object, e As System.EventArgs) Handles btnMoveNext.Click
        ' Move to the next record..
        objCurrencyManager.Position += 1
        ' Show the current record position..
        ShowPosition()
    End Sub

    Private Sub btnMoveLast_Click(sender As System.Object, e As System.EventArgs) Handles btnMoveLast.Click
        ' Set the record position to the last record..
        objCurrencyManager.Position = objCurrencyManager.Count - 1
        ' Show the current record position..
        ShowPosition()
    End Sub

    Private Sub txtRecordPosition_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtRecordPosition.TextChanged

    End Sub

    Private Sub ToolStripStatusLabel1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripStatusLabel1.Click

    End Sub

    Private Sub grdDataView_CellContentClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdDataView.CellContentClick

    End Sub
End Class

Thanks people!

Answer 1

 

Through Trial and Error I have managed to get the data to scroll in the Gridview by modifying this section:

    Private Sub BindFields()
        ' Clear any previous bindings..
        txtIDNumber.DataBindings.Clear()
        txtFirstName.DataBindings.Clear()
        txtLastName.DataBindings.Clear()
        txtDateHire.DataBindings.Clear()
        txtCommision.DataBindings.Clear()

        ' Add new bindings to the DataView object.
        txtIDNumber.DataBindings.Add("Text", objDataView, "REP_ID")
        txtFirstName.DataBindings.Add("Text", objDataView, "FIRST_NAME")
        txtLastName.DataBindings.Add("Text", objDataView, "LAST_NAME")
        txtDateHire.DataBindings.Add("text", objDataView, "HIRE_DATE")
        txtCommision.DataBindings.Add("Text", objDataView, "COMMISION")

        grdDataView.DataSource = objDataView
        grdDataView.DataBindings.Add("Text", objDataView, "", "REP_ID", DataViewRowState.CurrentRows)

        ' Display a ready status..
        ToolStripStatusLabel1.Text = "Ready"
    End Sub

However, I would like to display only the current selected data in the gridview not all of the data.

I will keep working on this but a little hep will be appreciated!

Thanks again!

Answer 2

Only wanting to display a single row in a datagridview is a bit of an odd requirement.

However, I threw this together using one of my databases and it seems to do what you want.  Obviously you will need to adapt it but basically the idea is that you bind the datagridview to a separate view on the datatable and filter that view as you change record.

Imports System.Data.SqlServerCe
Public Class Form1
   Dim DT As New DataTable
   Dim WithEvents CM As CurrencyManager
   Dim View As New DataView(DT)
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Using Conn As New SqlCeConnection("datasource=d:\tempsoftware\books.sdf")
         Using DA As New SqlCeDataAdapter("Select * from Authors", Conn)
            DA.Fill(DT)
         End Using
      End Using
      CM = CType(BindingContext(DT.DefaultView), CurrencyManager)
      TextBox1.DataBindings.Add("Text", DT.DefaultView, "AuthorID")
      TextBox2.DataBindings.Add("Text", DT.DefaultView, "Name")
      DataGridView1.DataSource = View
      CM.Position = 0
   End Sub
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      CM.Position += 1
      Dim ID As Integer = CInt(CType(CM.Current, DataRowView).Item("AuthorID"))
      View.RowFilter = "AuthorID=" & ID.ToString
   End Sub
End Class

Answer 3

Only wanting to display a single row in a datagridview is a bit of an odd requirement.

However, I threw this together using one of my databases and it seems to do what you want.  Obviously you will need to adapt it but basically the idea is that you bind the datagridview to a separate view on the datatable and filter that view as you change record.

Imports System.Data.SqlServerCe
Public Class Form1
   Dim DT As New DataTable
   Dim WithEvents CM As CurrencyManager
   Dim View As New DataView(DT)
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Using Conn As New SqlCeConnection("datasource=d:\tempsoftware\books.sdf")
         Using DA As New SqlCeDataAdapter("Select * from Authors", Conn)
            DA.Fill(DT)
         End Using
      End Using
      CM = CType(BindingContext(DT.DefaultView), CurrencyManager)
      TextBox1.DataBindings.Add("Text", DT.DefaultView, "AuthorID")
      TextBox2.DataBindings.Add("Text", DT.DefaultView, "Name")
      DataGridView1.DataSource = View
      CM.Position = 0
   End Sub
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      CM.Position += 1
      Dim ID As Integer = CInt(CType(CM.Current, DataRowView).Item("AuthorID"))
      View.RowFilter = "AuthorID=" & ID.ToString
   End Sub
End Class

Thanks for the input, as always, very valuable!