asked on

Databinding datagridview values to a textbox

Hi Experts! I need help databinding values from a datagridview to textboxes that the user could make changes and update. The datagridview1 is readonly. I orginally put this code in the datagridview1_RowEnter:
Me.txtEmployee.Text = .Cells("User_ID").Value
Me.txtCreateDate.Text = .Cells("Create_Date").Value
Me.txtSubmitDate.Text = .Cells("Submit_Date").Value

but I am pretty sure that will not allow me to update the dataset for the datagridview. Any Ideas?

VBRocks

Use a BindingSource as the DataSource for the DataGridView, and then use the same BindingSource
as the DataSource for the textboxes.

Here's an example:

Dim ds as New DataSet1() 'your dataset

Dim bs as New BindingSource()
bs.DataSource = ds
bs.DataMember = ds.Table1 'The name of the table to bind to

'Set the DataSource for the DataGridView
Me.DataGridView1.DataSource = bs 'Set DataSource for DataGridView

'Binding each textbox:
Me.txtEmployee.DataBindings.Add("Text", bs, "User_ID")
Me.txtCreateDate.DataBindings.Add("Text", bs, "Create_Date")
Me.txtSubmitDate.DataBindings.Add("Text", bs, "Submit_Date")

mdweaver

ASKER

Ok this is how it is loaded now to the datagridview
with dsv being the dataset

dsv = LoadMaster(userid)
DataGridView1.DataSource = dsv.Tables(0).DefaultView
so I would

dim dsv as new dataset
Dim bs as New BindingSource()
bs.DataSource = dsv
bs.DataMember = ds.? ' what do you mean by table name?

then Me.DataGridView1.DataSource = bs

Is this correct?

VBRocks

Very close! It would be like this:

dsv = LoadMaster(userid)

dim dsv as new dataset
Dim bs as New BindingSource()
bs.DataSource = dsv
bs.DataMember = dsv.Tables(0).TableName

Me.DataGridView1.DataSource = bs

VBRocks

Actually, the dsv declaration would be on top:

Dim dsv as DataSet
dsv = LoadMaster(userid)

Dim bs as New BindingSource()
bs.DataSource = dsv
bs.DataMember = dsv.Tables(0).TableName

Me.DataGridView1.DataSource = bs

mdweaver

ASKER

OK, that didn't work.
Here is what I changed the code to

Dim bs As New BindingSource()

'loads Userid into dropdown
ds = LoadUser()
cbEmployee.DataSource = ds.Tables(0)
cbEmployee.DisplayMember = "User_ID"

Try
userid = DirectCast(cbEmployee.SelectedItem, DataRowView).Item("User_ID")
'loads DataGridView1 with data
dsv = LoadMaster(userid)
bs.DataSource = dsv
bs.DataMember = dsv.Tables(0).TableName
'DataGridView1.DataSource = dsv.Tables(0).DefaultView
Me.DataGridView1.DataSource = bs
Me.txtEmployee.DataBindings.Add("Text", bs, "User_ID")
Me.txtCreateDate.DataBindings.Add("Text", bs, "Create_Date")
Me.txtSubmitDate.DataBindings.Add("Text", bs, "Submit_Date")

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)

End Try

DataGridView2.Visible = False
DataGridView1.Visible = True

'Formats columnNames in Gridview
ColumnNames()

ToolStripStatusLabel1.Text = "Ready"

VBRocks

That looks perfect! What didn't work?

mdweaver

ASKER

its not showing the values of the fields in the textboxes.

VBRocks

First of all, are the names of the columns that we are binding to in the DataGridView?

"User_ID" and "Create_Date" and "Submit_Date"

Those must be correct, or the binding won't work.

And also, is there data in the datagridview? Try selecting different rows and see if the data displays
in the text boxes.

mdweaver

ASKER

yes the columns are User_ID, Create_Date and Submit_Date

VBRocks

That doesn't make much since at all.

Can you comment out your current code and test out this little demo I created? It works perfect for me:

Dim dsv As New DataSet()
dsv.Tables.Add("Table1")

With dsv.Tables(0)
.Columns.Add("Col1")

'Add 5 rows
For i As Int16 = 1 To 5
.Rows.Add("Item " & i)
Next

End With

'loads DataGridView1 with data
Dim bs As New BindingSource()
bs.DataSource = dsv
bs.DataMember = dsv.Tables(0).TableName

Me.DataGridView1.DataSource = bs
Me.TextBox1.DataBindings.Add("Text", bs, "Col1")

mdweaver

ASKER

the example worked but my application will not

VBRocks

Weird. Does the DataGridView1 have data displayed in it?

Also, double-check and make sure the DataSource for the DataGridView is:
Me.DataGridView1.DataSource = bs

And make sure it is not being set anywhere else. Because if it is, then it will not be synchronized.

VBRocks

If that doesn't work. Is there any way you can zip it, and upload it so I can take a look?

Experts-Exchange has an upload website:

http://www.ee-stuff.com/accessLogin.php?returnURL=%2FExpert%2Findex.php%3F

VBRocks

Ok, let's try a different approach. Let's use the index of the column in the DataGridView that we
want to bind it to:

userid = DirectCast(cbEmployee.SelectedItem, DataRowView).Item("User_ID")

'loads DataGridView1 with data
dsv = LoadMaster(userid)
bs.DataSource = dsv
bs.DataMember = dsv.Tables(0).TableName

Me.DataGridView1.DataSource = bs

'Bind to "User_ID" column in DataGridView (change index to correct column index)
Me.txtEmployee.DataBindings.Add("Text", bs, Me.DataGridView1.Columns(0).DataPropertyName)

'Bind to "Create_Date" column in DataGridView (change index to correct column index)
Me.txtEmployee.DataBindings.Add("Text", bs, Me.DataGridView1.Columns(1).DataPropertyName)

'Bind to "Submit_Date" column in DataGridView (change index to correct column index)
Me.txtEmployee.DataBindings.Add("Text", bs, Me.DataGridView1.Columns(2).DataPropertyName)

mdweaver

ASKER

Sorry, I had to walk away. I tried that and it didn't work too.... I can paste my code in but I can't zip

Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data.SqlClient.SqlParameter
Imports Infragistics.Win.UltraWinGrid
Imports System.Windows.Forms
Public Class frmReports
    Dim da As New SqlDataAdapter
    Dim cb As SqlCommandBuilder
    Dim ds As DataSet
    Dim cn As New SqlConnection(My.Settings.ConnectionString)
    Dim dsv As DataSet
    Dim userid As String
    Dim bs As New BindingSource()
 
   
    Private Sub frmReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        
        'loads Userid into dropdown
        ds = LoadUser()
        cbEmployee.DataSource = ds.Tables(0)
        cbEmployee.DisplayMember = "User_ID"
        ToolStripStatusLabel1.Text = "Ready"
        cbEmployee.SelectedIndex = 0
        ColumnNames()
 
        'loads DataGridView1 with data
 
        Try
 
            userid = DirectCast(cbEmployee.SelectedItem, DataRowView).Item("User_ID")
            'loads DataGridView1 with data
            dsv = LoadMaster(userid)
 
            Dim bs As New BindingSource()
            bs.DataSource = dsv
            bs.DataMember = dsv.Tables(0).TableName
            'DataGridView1.DataSource = dsv.Tables(0).DefaultView
            Me.DataGridView1.DataSource = bs
            Me.txtEmployee.DataBindings.Add("Text", bs, Me.DataGridView1.Columns(0).DataPropertyName)
            Me.txtCreateDate.DataBindings.Add("Text", bs, Me.DataGridView1.Columns(11).DataPropertyName)
            Me.txtSubmitDate.DataBindings.Add("Text", bs, Me.DataGridView1.Columns(9).DataPropertyName)
 
 
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
 
        End Try
 
        DataGridView2.Visible = False
        DataGridView1.Visible = True
 
 
 
        'Formats columnNames in Gridview
        ColumnNames()
    End Sub
    
 
    
 
 
 
 
 
    Public Function LoadUser() As DataSet
        'Executes stored procedure to load User_ID's
        Dim Parms(1) As SqlClient.SqlParameter
        Return (ExecQuery("usp_Expense_Report_Cards_Select", Parms))
 
    End Function
    Public Function LoadMaster(ByVal userid As String) As DataSet
        'Executes stored procedure to load Master table by User_ID
        Dim Parms(0) As SqlClient.SqlParameter
        Parms(0) = New SqlParameter("@User_ID", userid)
        Return (ExecQuery("usp_Expense_Report_Master_Select_UserID", Parms))
    End Function
    Public Function LoadDetails(ByVal MasterRowID As String) As DataSet
        'Executes stored procedure to load User_ID's
        Dim Parms(0) As SqlClient.SqlParameter
        Parms(0) = New SqlParameter("@Master_Row_ID", MasterRowID)
        Return (ExecQuery("usp_Expense_Report_Detail_Select_RowID", Parms))
 
 
    End Function
 
#Region "SQL Helper Methods"
 
 
    '****************************************************************************************
    ' ExecQuery
    ' ABSTRACT: Executes a stored procedure against the Eisemann database and returns
    '   a NEW Dataset with the selected data.
    '
    ' INPUT PARMS:  ProcedureName   Name of Stored Procedure to execute
    '               Parms           Array of SqlParameter objects that will be passed into the
    '                               stored procedure.
    '
    ' RETURNS:      DataSet populated with results from stored procedure execution.
    '
    '****************************************************************************************
    Private Overloads Function ExecQuery(ByVal ProcedureName As String, ByVal Parms As SqlParameter()) As DataSet
        Dim _cmdSqlCommand As New SqlCommand
        Dim _adpAdapter As New SqlDataAdapter
        Dim _cnnMyConnection As New SqlConnection(My.Settings.ConnectionString)
        Dim dsDataSet As New DataSet
        ' Configure the SqlCommand object
        With _cmdSqlCommand
            .CommandType = CommandType.StoredProcedure      'Set type to StoredProcedure
            .CommandText = ProcedureName                    'Specify stored procedure to run
            .Connection = _cnnMyConnection
            ' Clear any previous parameters from the Command object
            Call .Parameters.Clear()
 
            ' Loop through parmameter collection adding parameters to the command object
            If Not (Parms Is Nothing) Then
                For Each sqlParm As SqlParameter In Parms
                    If Not (sqlParm Is Nothing) Then
                        _cmdSqlCommand.Parameters.Add(sqlParm)
                    End If
                Next
            End If
        End With
        If _cnnMyConnection.State <> ConnectionState.Open Then
            _cnnMyConnection.Open()
        End If
 
        ' Configure Adapter to use newly created command object and fill the dataset.
        _adpAdapter.SelectCommand = _cmdSqlCommand
        _adpAdapter.Fill(dsDataSet)
        _cnnMyConnection.Close()
        Return dsDataSet
 
    End Function
 
    '****************************************************************************************
    ' ExecNonQuery
    ' ABSTRACT: Executeds a non-query without any parameters.
    '
    ' INPUT PARMS: ProcedureName    stored procedure to execute.
    '
    ' RETURNS:  Integer indicating how many rows were affected by the non-query.
    '
    ' Copyright © 2005 by Corning, Inc.
    '****************************************************************************************
    Private Overloads Function ExecNonQuery(ByVal ProcedureName As String) As Integer
        Return ExecNonQuery(ProcedureName, Nothing)
    End Function
 
    '****************************************************************************************
    ' ExecNonQuery
    ' ABSTRACT: Executes a non-query stored procedure agains the Eisemann database.
    '
    ' INPUT PARMS:  ProcedureName   Name of stored procedure to execute
    '               Parms           Collection of SqlParameter objects used as arguments for
    '                               the stored procedure.
    '
    ' RETURNS:      An integer containing the number of rows affected by the Stored Procedure.
    '
    ' Copyright © 2005 by Corning, Inc.
    '****************************************************************************************
    Private Overloads Function ExecNonQuery(ByVal ProcedureName As String, ByVal Parms As SqlParameter()) As Integer
        Dim _cmdSqlCommand As New SqlCommand
        Dim _adpAdapter As New SqlDataAdapter
        Dim _cnnMyConnection As New SqlConnection(My.Settings.ConnectionString)
        Dim intRowsAffected As Integer
 
        ' Configure the _cmdSqlCommand object
        With _cmdSqlCommand
            .CommandType = CommandType.StoredProcedure  'Set type to Stored Procedure   
            .CommandText = ProcedureName                'Specify procedure to run
 
            ' Clear any previous parameters from the command object
            Call .Parameters.Clear()
 
            ' Loop through parmameter collection, if defined, adding parameters to the command object
            If Not (Parms Is Nothing) Then
                For Each sqlParm As SqlParameter In Parms
                    _cmdSqlCommand.Parameters.Add(sqlParm)
                Next
            End If
        End With
        If _cnnMyConnection.State <> ConnectionState.Open Then
            Call _cnnMyConnection.Open()
        End If
        ' Execute the procedure
        intRowsAffected = _cmdSqlCommand.ExecuteNonQuery()
 
        Return intRowsAffected                          'Return the number of rows affected by procedure
 
    End Function
#End Region
 
 
 
 
    Public Function ColumnNames()
        'Formats each column with User friendly headings, Readonly & Hides Some Columns
        Try
            Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.CornflowerBlue
            Me.DataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.Raised
            Me.DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            Me.DataGridView1.Columns(0).Name = "ROW_ID"
            Me.DataGridView1.Columns(0).HeaderText = "Row ID"
            Me.DataGridView1.Columns(0).Visible = True
            Me.DataGridView1.Columns(0).ReadOnly = True
            Me.DataGridView1.Columns(1).Name = "User_ID"
            Me.DataGridView1.Columns(1).HeaderText = "User ID"
            Me.DataGridView1.Columns(1).Visible = False
            Me.DataGridView1.Columns(1).ReadOnly = True
            Me.DataGridView1.Columns(2).Name = "Report_Month"
            Me.DataGridView1.Columns(2).HeaderText = "Report Month"
            Me.DataGridView1.Columns(2).ReadOnly = True
            Me.DataGridView1.Columns(3).Name = "Report_Year"
            Me.DataGridView1.Columns(3).HeaderText = "Report Year"
            Me.DataGridView1.Columns(3).ReadOnly = True
            Me.DataGridView1.Columns(4).Name = "Card_Hash"
            Me.DataGridView1.Columns(4).HeaderText = "Card Hash"
            Me.DataGridView1.Columns(4).Visible = False
            Me.DataGridView1.Columns(4).ReadOnly = True
            Me.DataGridView1.Columns(5).Name = "Manager_Approval"
            Me.DataGridView1.Columns(5).HeaderText = "Manager Approval"
            Me.DataGridView1.Columns(5).Visible = False
            Me.DataGridView1.Columns(5).ReadOnly = True
            Me.DataGridView1.Columns(6).Name = "Accounting_Approval"
            Me.DataGridView1.Columns(6).HeaderText = "Accounting Approval"
            Me.DataGridView1.Columns(6).Visible = False
            Me.DataGridView1.Columns(6).ReadOnly = True
            Me.DataGridView1.Columns(7).Name = "Report_Processed_Flag"
            Me.DataGridView1.Columns(7).HeaderText = "Report Processed Flag"
            Me.DataGridView1.Columns(7).Visible = False
            Me.DataGridView1.Columns(7).ReadOnly = True
            Me.DataGridView1.Columns(8).Name = "Submit_Date"
            Me.DataGridView1.Columns(8).HeaderText = "Submit Date"
            Me.DataGridView1.Columns(8).ReadOnly = True
            Me.DataGridView1.Columns(9).Name = "ReSubmit_Date"
            Me.DataGridView1.Columns(9).HeaderText = "ReSubmit Date"
            Me.DataGridView1.Columns(9).Visible = False
            Me.DataGridView1.Columns(9).ReadOnly = True
            Me.DataGridView1.Columns(10).Name = "Record_Type"
            Me.DataGridView1.Columns(10).HeaderText = "Record Type"
            Me.DataGridView1.Columns(10).ReadOnly = True
            Me.DataGridView1.Columns(10).Visible = False
            Me.DataGridView1.Columns(11).Name = "Create_Date"
            Me.DataGridView1.Columns(11).HeaderText = "Create Date"
            Me.DataGridView1.Columns(11).ReadOnly = True
            Me.DataGridView1.Columns(12).Name = "Odometer_Start"
            Me.DataGridView1.Columns(12).HeaderText = "Odometer Start"
            Me.DataGridView1.Columns(12).Visible = False
            Me.DataGridView1.Columns(12).ReadOnly = True
            Me.DataGridView1.Columns(13).Name = "Odometer_End"
            Me.DataGridView1.Columns(13).HeaderText = "Odometer End"
            Me.DataGridView1.Columns(13).Visible = False
            Me.DataGridView1.Columns(13).ReadOnly = True
            Me.DataGridView1.Columns(14).Name = "Personal_Miles"
            Me.DataGridView1.Columns(14).HeaderText = "Personal Miles"
            Me.DataGridView1.Columns(14).Visible = False
            Me.DataGridView1.Columns(14).ReadOnly = True
            Me.txtEmployee.ReadOnly = True
        Catch ex As Exception
 
        End Try
 
 
 
 
 
    End Function
    Public Function ColumnNames1()
        Try
            'Formats each column with User friendly headings, Readonly & Hides Some Columns
            Me.DataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            Me.DataGridView2.AlternatingRowsDefaultCellStyle.BackColor = Color.CornflowerBlue
            Me.DataGridView2.Columns(0).Visible = False
            Me.DataGridView2.Columns(1).Visible = False
            Me.DataGridView2.Columns(2).Name = "Transaction_Date"
            Me.DataGridView2.Columns(2).HeaderText = "Transaction Date"
            Me.DataGridView2.Columns(2).Width = "150"
            Me.DataGridView2.Columns(3).Name = "Posted_Date"
            Me.DataGridView2.Columns(3).HeaderText = "Posted Date"
            Me.DataGridView2.Columns(6).Name = "Transaction_Detail"
            Me.DataGridView2.Columns(6).HeaderText = "Transaction Detail"
            Me.DataGridView2.Columns(6).Width = "150"
            Me.DataGridView2.Columns(8).Name = "Transaction_Amount"
            Me.DataGridView2.Columns(8).HeaderText = "Transaction Amount"
            Me.DataGridView2.Columns(8).Width = "150"
 
 
        Catch ex As Exception
 
        End Try
 
 
    End Function
 
 
    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        Me.Close()
    End Sub
 
 
 
    Private Sub DataGridView1_RowHeaderMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.RowHeaderMouseClick
 
        Me.DataGridView2.Visible = True
        Me.btnPrint.Visible = True
        Me.btnUpdate.Visible = True
        'Dim MyRow As Infragistics.Win.UltraWinGrid.UltraGridRow
 
 
        Try
            ' Sets MasterRowID with the value in the datagridview
            Dim MasterRowID As Integer = Convert.ToInt32(Me.DataGridView1.CurrentCell.Value)
 
            Dim view As DataView = Me.DataGridView2.DataSource
 
 
            'MsgBox(MasterRowID)
            'Loads Details in the datagridview by the rowid
            ds = LoadDetails(MasterRowID)
            DataGridView2.DataSource = ds.Tables(0).DefaultView
            'DataGridView1.Refresh()
 
 
 
        Catch ex As Exception
            'MsgBox("Please Select a Valid Report")
        End Try
 
        ColumnNames1()
 
    End Sub
 
    Private Sub cbEmployee_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbEmployee.SelectedIndexChanged
        'Dim view As DataView = Me.DataGridView1.DataSource
 
        ' Assigns User_ID to the userid
        userid = DirectCast(cbEmployee.SelectedItem, DataRowView).Item("User_ID")
        Try
            userid = DirectCast(cbEmployee.SelectedItem, DataRowView).Item("User_ID")
            'loads the datagridview with the information from the Master Report by Userid selected
 
            dsv = LoadMaster(userid)
            DataGridView1.DataSource = dsv.Tables(0).DefaultView
 
 
        Catch ex As Exception
 
        End Try
    End Sub
 
 
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        ' cb = New SqlCommandBuilder(da)
        ' da.SelectCommand = New SqlCommand()
 
        Dim res As String
 
        res = MsgBox("Do you wish to Update?", MsgBoxStyle.YesNo + MsgBoxStyle.Information, "Confirmation")
        If res = vbYes Then
            Try
                DataGridView2.EndEdit()
                da.Update(ds.Tables(0))
                ds.AcceptChanges()
 
 
            Catch ex As Exception
                MsgBox("Update Failed: " & ex.Message)
 
            End Try
 
 
        End If
    End Sub
 
    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        ' Prints Report
        PrintDocument1.Print()
    End Sub
    Private Sub printdocument1_PrintPage(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
 
        Dim myPaintArgs As New PaintEventArgs(e.Graphics, New Rectangle(New Point(0, 0), Me.Size))
        Me.InvokePaint(DataGridView2, myPaintArgs)
 
 
    End Sub
End Class

Open in new window

VBRocks

Hmmm... How many rows are in the grid?

Dim bs As New BindingSource()
bs.DataSource = dsv
bs.DataMember = dsv.Tables(0).TableName

MsgBox(bs.Count)

mdweaver

ASKER

ok msgbox said 0 and I think I know why

the first user is my dropdown does not have any reports in datagridview. so datagridview1 doesn't load with data at the time of load. I do have the procedure called:

Private Sub cbEmployee_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbEmployee.SelectedIndexChanged
'Dim view As DataView = Me.DataGridView1.DataSource

' Assigns User_ID to the userid
userid = DirectCast(cbEmployee.SelectedItem, DataRowView).Item("User_ID")
Try
userid = DirectCast(cbEmployee.SelectedItem, DataRowView).Item("User_ID")
'loads the datagridview with the information from the Master Report by Userid selected

dsv = LoadMaster(userid)
DataGridView1.DataSource = dsv.Tables(0).DefaultView

Catch ex As Exception

End Try
End Sub

so it reloads everytime the user_id is selected could that have anything to do with it?

ASKER CERTIFIED SOLUTION

VBRocks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mdweaver

ASKER

You Rock!!! That worked and fixed my problem