Printable View
Hello,
I have a table called "EmployeeMainFile" and i have a lot of columns. In my Data Grid view I'm only displaying the EmpCode, EmpName and PassportNo.
My question is if I clicked on one employee row I want to show all of his data on the textboxex, comboboxes, etc., not only the displaying column in the Data Grid View for example his HireDate, Salary and etc..
What is the best way to do it?
Thanks in advance.
I am definitely only at the "beginner to intermediate" level when it comes to database coding, but I would re-query the database table to get the data for the one record (row) selected. To fill the Datagridview, I would get only the primary key (assuming you have one) plus the columns you want to show. When the user clicks on a row, use the primary key to query the database table again to get the required columns.
In case it helps, here is some example code. In my case I do show most of the available columns in the DGV but I still prefer to re-query to populate my textboxes:
Code:Private Sub Connect_DGV()
Dim myConn As New SQLiteConnection(GsConString)
myConn.Open()
Dim cmd As New SQLiteCommand With {
.Connection = myConn
}
Dim sql As String = "Select LogID,strftime('%d/%m/%Y',DateTimeStart),strftime('%H%M',DateTimeStart),Callsign,Fband,
Frequency,Mode,Sent,Recv,Locator,Postcode,OpName,Notes,Valid,MyCall,MyLoc,DateTimeEnd
from tblLog order by DateTimeStart"
cmd.CommandText = sql
Dim rdr As Data.SQLite.SQLiteDataReader = cmd.ExecuteReader
Dim dt As New DataTable
dt.Load(rdr)
rdr.Close()
myConn.Close()
dgvLog.DataSource = dt
End Sub
Private Sub Show_Complete_Record(iQSOid as integer)
Using myConn As New SQLiteConnection(GsConString)
myConn.Open()
Dim cmd As New SQLiteCommand With {
.Connection = myConn,
.CommandText = "SELECT * FROM tblLog WHERE LogID='" & iQSOid & "'"
}
Using reader As SQLiteDataReader = cmd.ExecuteReader()
If reader.HasRows Then
reader.Read()
txtDateTimeStart.Text = reader.Item("DateTimeStart").ToString
txtDateTimeEnd.Text = reader.Item("DateTimeEnd").ToString
txtCallsign.Text = reader.Item("Callsign").ToString
' Many more textboxes here ...
chkValid.Checked = CBool(reader.Item("Valid"))
End If
End Using
End Using
End Sub
Why? All the info is already in the DataTable. There's no need to go back to the database to get what you already know.Quote:
Originally Posted by paulg4ije
The easiest way would be to use a BindingSource... set the DataSource of it to hte DataTable... then bind that to the grid... then bind the textboxes to the BS, setting the fields accordingly. As the user selects . row, the BindingSource should keep track of that, and update the bindings to the textboxes accordingly.
-tg
OK: make that "beginner" only ;-)Quote:
Originally Posted by techgnome
My DGV and textboxes are on different forms and my method keeps things self-contained and of local scope. Perhaps I'll try it a different way next time. Always so many ways to handle database tables ...
Ok, so pull the data from the DGV and pass it to the new form and let that display it. Again, if you have the data, there is no reason to go back to the database.Quote:
Originally Posted by paulg4ije
-tg
Thank you for your reply both of you.
@techgnome Do you have any sample code?
Thanks again.
It's easy. In my example you just need to change [bindingsource] and "fieldname"
Code:TextBox1.DataBindings.Add("Text", [bindingsource], "fieldname")
To pass a bindingsource to a form (when you open the form)...
Code:Public Class Form2
Private bs as BindingSource
Public Sub New(source as BindingSource)
Me.bs = source
End Sub
End Class
Code:Dim frm as new Form2(originalBindingSource)
frm.ShowDialog
Thank you .paul. for your reply.
The Data grid view and the text boxes are in the same form, so it will remain the same code anyway?
Thanks
Just use the databindings code I showed you, using the same datasource as the dgv, with each text box binding having the appropriate field name
The OP said he had just three columns in his DGV but many more columns to load from a selected record in to his textboxes. As I said, lots of ways to go with this.Quote:
Originally Posted by techgnome
Yeah that's right paulg4ije.
I'm trying what .paul. provide but still no result. :(
Using part of paulg4ije's code example
Code:Private Sub Connect_DGV()
Dim myConn As New SQLiteConnection(GsConString)
myConn.Open()
Dim cmd As New SQLiteCommand With {
.Connection = myConn
}
Dim sql As String = "Select LogID,strftime('%d/%m/%Y',DateTimeStart),strftime('%H%M',DateTimeStart),Callsign,Fband,
Frequency,Mode,Sent,Recv,Locator,Postcode,OpName,Notes,Valid,MyCall,MyLoc,DateTimeEnd
from tblLog order by DateTimeStart"
cmd.CommandText = sql
Dim rdr As Data.SQLite.SQLiteDataReader = cmd.ExecuteReader
Dim dt As New DataTable
dt.Load(rdr)
rdr.Close()
myConn.Close()
dgvLog.DataSource = dt
TextBox1.DataBindings.Add("Text", dt, "Sent") ' dt is the datasource. "Sent" is a fieldname mentioned in the sql string
End Sub
He also said the grid and text boxes were on different forms... now they're on the same form....Quote:
Originally Posted by paulg4ije
Even still... select all of the columns you're going to use... send it to the BindingSource... bind that to the Grid, hide the columns you don't want, then bind the BindingSource to the textboxes as shown... That is the easiest, most efficientway to do this. Otherwise you're using two datatabes and trying to coordinate one with the other... that's not the way to go with it. Because here's the next thing that will happen... after the data is updated in the textboxes, getting those changes into the grid... which means either finding the row and updating it (which... you just did through the bindingSource since it's bound to the textboxes, or 2) worse yet (even though I see it all the time) goig back to the database (again) and refreshing the datatable for the grid.... geeee... if only there was a way to just do the update once, and not have to go through all that... wait... there is... use ONE datatable with all of the data... bind it to a BindingSource... which can then be bound to both the grid and the textboxes and whe the textboxes are updated the grid will update along with it.
Yes, it takes a bit of thought and setup and coding ahead of time, but the payoffs are worth it. Depending on circumstances, some of it can even be done at design time...
-tg