Hidden values in DataTable; DataGridView
I suck at working with DataGridView and datasets, but I think working through this problem will help me get a better understanding. I've been trying to find applicable reading on this stuff, but its pretty hard to sort through.
I have a mySQL database I am connecting to, and I want to fill a datagridview with the results. I am building a much more complex SQL statement, but this works for my example.
Code:
dim sSQL as string = "SELECT date, location, id FROM tbl_docs"
Dim connStr As String = String.Format("Server={0};Port={1};Database={2};Uid={3};Pwd={4};", DB_SERVER, DB_PORT, DB_DATABASE, DB_USER, DB_PASS)
Try
conn = New MySqlConnection(connStr)
conn.Open()
data = New DataTable
da = New MySqlDataAdapter(sSQL, conn)
cb = New MySqlCommandBuilder(da)
da.Fill(data)
dataResults.DataSource = data
I basically just need the id to be hidden, so that when I click on either the location or date cell, I get that rows ID. I don't want the id row in the DataGridView. Also, I want to be able to format the date differently than it is stored in the mySQL database. What that boils down to is being able to manipulate data in the DataTable before it gets bound to the DataGridView.
Probably a easy question to answer... Any ideas?
Re: Hidden values in DataTable; DataGridView
You just set the datagridviewcolumn.Visible property to false after you bind the datatable to it
Code:
dataResults.DataSource = data
dataResults.Columns("id").Visible = False
You can also create the 2 columns in your datagridview at design time, and set DataPropertyName property of the column to the field/column name of your datatable, and set the DGV.AutoGenerateColumns = False. That way, it won't create any column automatically but rather uses only the columns that you manually created.
Re: Hidden values in DataTable; DataGridView
Haha! That was easy... I got that working fine, now I can grab any column I want when I click on a cell with:
Code:
MessageBox.Show(dataResults.CurrentRow.Cells("id").Value)
or any other field. I just need to set visible=false. I didn't know you could just change properties of columns like that. Like I said, I've never done much with this DataSource stuff.
Now my only question is, how do I change the format of the Date field. Right now it is stored as 20080627 in the mySQL database (I know, this itself is a problem. I'm working on it...) but I want it to display June 27, 2008.
Re: Hidden values in DataTable; DataGridView
What is the data type the date field is stored in your sql database?
Re: Hidden values in DataTable; DataGridView
Ok, I didn't see the other post on the first page. It looks like it will be easiest to fix the formatting problem in the database first and then set the DefaultCellStyle. Right now the date field is just a integer data type, so I will fix that first. mySQL stores dates like 2008-06-27, so I will have to convert some stuff.
Thanks stanav