Question populating data from datagridview into textboxes

[elianeasmar]

Hello guys i have a problem. i have a datagridview with textboxes. i insert-update-delete records from database. Everything works fine. when i click on a cell in the grid. the information about this person are populated into the textboxes. but when i click on the newly inserted record it won't display the informations into texboxes. here is my code. What could the problem be?

Private Sub DataGridView1_RowEnter(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
                    Try
            If e.RowIndex < 0 Then Exit Sub
            txtserial.Text = DataGridView1.Rows(e.RowIndex).Cells(0).Value
            lblcustomer.Text = "Member: " & DataGridView1.Rows(e.RowIndex).Cells("Name").Value & "   " & DataGridView1.Rows(e.RowIndex).Cells("familyName").Value
            Dim cmd As SqlCommand
            Dim myDA As SqlDataAdapter
            Dim myDataSet As DataSet
            Dim strsql As String
            Dim i As Integer = 0
            strsql = "select * from membersvw where serial = " & Val(txtserial.Text)
            cmd = New SqlCommand(strsql, mcon)
            If mcon.State = ConnectionState.Closed Then mcon.Open()
            myDA = New SqlDataAdapter(cmd)
            myDataSet = New DataSet()
            myDA.Fill(myDataSet, "membersvw")
            If myDataSet.Tables("membersvw").Rows.Count > 0 Then
                txtserial.Text = myDataSet.Tables("membersvw").Rows(0).Item("serial").ToString
                txtname.Text = myDataSet.Tables("membersvw").Rows(0).Item("name").ToString
                txtfamilyname.Text = myDataSet.Tables("membersvw").Rows(0).Item("familyname").ToString
                txtphone.Text = myDataSet.Tables("membersvw").Rows(0).Item("phone").ToString
                txtmobile.Text = myDataSet.Tables("membersvw").Rows(0).Item("mobile").ToString
                txtfax.Text = myDataSet.Tables("membersvw").Rows(0).Item("fax").ToString
                txtemail.Text = myDataSet.Tables("membersvw").Rows(0).Item("email").ToString
                txtwebsite.Text = myDataSet.Tables("membersvw").Rows(0).Item("website").ToString
                txtaddress.Text = myDataSet.Tables("membersvw").Rows(0).Item("address").ToString
                txtspace.Text = myDataSet.Tables("membersvw").Rows(0).Item("space").ToString
                cmbtimezone.Text = myDataSet.Tables("membersvw").Rows(0).Item("timezonename").ToString
            End If
            If TabControl1.SelectedTab.Name = "TabPage2" Then
                clearcars()
                fillcargrid()
            ElseIf TabControl1.SelectedTab.Name = "TabPage3" Then
                clearpayments()
                fillpaymentgrid()
            End If
            myDA.Dispose()
            myDataSet.Dispose()
            'mcon.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

Thank you for your help.

 

[jmcilhinney]

You can get rid of that code in its entirety. You don't need any code at all to populate the TextBoxes. When you populate the DataTable, simply bind it to both the DataGridView and the TextBoxes. That way, whenever you select a row in the grid, it's contents will be automatically display in the TextBoxes courtesy of data-binding, which is the whole point, e.g.

myBindingSource.DataSource = myDataTable
myDataGridView.DataSource = myBindingSource

myTextBox.DataBindings.Add("Text", myBindingSource, "ColumnName")

 

[jmcilhinney]

I just realised that you are actually retrieving more data when the selection changes. If that's what you want to do then what I said still stands, but you would only populate the columns you want to see in the grid at the start, populating all other columns with NULLs. On the CurrentChanged event of the BindingSource, you can check whether the current row contains data or not and, if it doesn't, query the database again to populate the already selected row.

 

[elianeasmar]

ok. Actually i don't like using databindings.
I intend to write it all but the thing is that it's reading from a view. and i can't see the newly inserted record in the view,but i can see them in the table.
so i am going to try someting. write the sql query to read from the tables. not the view.

 

[jmcilhinney]

ok. Actually i don't like using databindings.
I intend to write it all

That's your decision but it's misguided.

 

[elianeasmar]

Well i did try my method. Not working. So Back to your method. I'm gonna try it now. Hope it will give me positive result.
thx

 

[elianeasmar]

nothing appears in my datagrid

    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        If e.RowIndex < 0 Then Exit Sub
        txtserial.Text = DataGridView1.Rows(e.RowIndex).Cells("serial").Value
        lblcustomer.Text = "Member: " & DataGridView1.Rows(e.RowIndex).Cells("Name").Value & "   " & DataGridView1.Rows(e.RowIndex).Cells("familyName").Value
        Dim cmd As SqlCommand
        Dim myDA As SqlDataAdapter
        Dim table As New DataTable
        Dim strsql As String
        strsql = "select * from membersvw where serial = " & Val(txtserial.Text)
        cmd = New SqlCommand(strsql, mcon)
        If mcon.State = ConnectionState.Closed Then mcon.Open()
        myDA = New SqlDataAdapter(cmd)
        myDA.Fill(table)
        Dim bs As New BindingSource
        bs.DataSource = table
        DataGridView1.DataSource = bs
        txtname.DataBindings.Add("Text", bs, "name")
        txtfamilyname.DataBindings.Add("Text", bs, "familyname")
        txtname.DataBindings.Add("Text", bs, "name")
        txtaddress.DataBindings.Add("Text", bs, "address")
        txtphone.DataBindings.Add("Text", bs, "phone")
        txtmobile.DataBindings.Add("Text", bs, "mobile")
        txtfax.DataBindings.Add("Text", bs, "Fax")
        txtemail.DataBindings.Add("Text", bs, "Email")
        txtwebsite.DataBindings.Add("Text", bs, "Website")


    End Sub

 

[elianeasmar]

It worked finally It took me all day.
I got back to my original method. It'a a bit ugly, you are right but it worked.
Here it is.

Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        Try
            If e.RowIndex < 0 Then Exit Sub
            txtserial.Text = DataGridView1.Rows(e.RowIndex).Cells("serial").Value
            lblcustomer.Text = "Member: " & DataGridView1.Rows(e.RowIndex).Cells("Name").Value & "   " & DataGridView1.Rows(e.RowIndex).Cells("familyName").Value
            Dim cmd As SqlCommand
            Dim myDA As SqlDataAdapter
            Dim myDataSet As DataSet
            Dim strsql As String
            Dim i As Integer = 0


            strsql = "SELECT Members.*,"
            strsql &= "  membercars.make, membercars.model, membercars.plate, membercars.color, membercars.tag, memberpayments.month,"
            strsql &= "  memberpayments.amount, memberpayments.year, memberpayments.vat, memberpayments.expirydate,"
            strsql &= "  MemberTimeZone.Name as TimeZoneName"
            strsql &= " FROM         membertimezone RIGHT OUTER JOIN"
            strsql &= " members ON Membertimezone.serial = members.timezone LEFT OUTER JOIN"
            strsql &= " membercars ON members.serial = membercars.memberid LEFT OUTER JOIN"
            strsql &= " memberpayments ON members.serial = memberpayments.memberid"
            strsql &= " Where Members.Serial = " & Val(txtserial.Text)
            cmd = New SqlCommand(strsql, mcon)
            If mcon.State = ConnectionState.Closed Then mcon.Open()
            myDA = New SqlDataAdapter(cmd)
            myDataSet = New DataSet()
            myDA.Fill(myDataSet, "membersvw")
            If myDataSet.Tables("membersvw").Rows.Count > 0 Then
                txtserial.Text = myDataSet.Tables("membersvw").Rows(0).Item("serial").ToString
                txtname.Text = myDataSet.Tables("membersvw").Rows(0).Item("name").ToString
                txtfamilyname.Text = myDataSet.Tables("membersvw").Rows(0).Item("familyname").ToString
                txtphone.Text = myDataSet.Tables("membersvw").Rows(0).Item("phone").ToString
                txtmobile.Text = myDataSet.Tables("membersvw").Rows(0).Item("mobile").ToString
                txtfax.Text = myDataSet.Tables("membersvw").Rows(0).Item("fax").ToString
                txtemail.Text = myDataSet.Tables("membersvw").Rows(0).Item("email").ToString
                txtwebsite.Text = myDataSet.Tables("membersvw").Rows(0).Item("website").ToString
                txtaddress.Text = myDataSet.Tables("membersvw").Rows(0).Item("address").ToString
                txtspace.Text = myDataSet.Tables("membersvw").Rows(0).Item("space").ToString
                cmbtimezone.Text = myDataSet.Tables("membersvw").Rows(0).Item("timezonename").ToString
            End If
            If TabControl1.SelectedTab.Name = "TabPage2" Then
                clearcars()
                fillcargrid()
            ElseIf TabControl1.SelectedTab.Name = "TabPage3" Then
                clearpayments()
                fillpaymentgrid()
            End If
            myDA.Dispose()
            myDataSet.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

 

[jmcilhinney]

Well, I am right but how would you know, because you're still not doing what I said?