Answered Problem with output of query which is shown in a DataGridView

PDS8475

Active member
Joined
Jun 25, 2019
Messages
27
Programming Experience
Beginner
Hi
I have a problem with the following query
"Select * FROM ItemsForSale INNER JOIN Engineering ON Engineering.Serial = ItemsForSale.Serial WHERE Engineering.Name = @s"
I am using this to filter the results and it fetches the right results in the DataGridView, However the first 4 columns are named "TableName.ColumnName" and the rest are just "ColumnName"
How can I stop the first four columns from showing the table name.

There is another 3 DataGridViews on the form using the same code but with a different query and they all work fine. I have even copied the code from this and the code for filtering a working dataGridView and they are identical apart from the query.

The code that I am using is below

C#:
 string iConString = System.IO.File.ReadAllText("FixIT.con");
                iConString = iConString.Replace(System.Environment.NewLine, string.Empty);
               
                System.Data.OleDb.OleDbConnection iconnn = new System.Data.OleDb.OleDbConnection();
                string iconnsString = "Provider = Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + iConString;
                try
                {

                    using (OleDbConnection iconnection = new OleDbConnection(iconnsString))
                    {

                     
                        using (OleDbCommand icommand = new OleDbCommand("Select * FROM ItemsForSale INNER JOIN Engineering ON Engineering.Serial = ItemsForSale.Serial WHERE Engineering.Name = @s", iconnection))
                        {

                            icommand.CommandType = CommandType.Text;
                            icommand.Parameters.AddWithValue(@s, Details1_textBox.Text);
                            using (OleDbDataAdapter iDA = new OleDbDataAdapter(icommand))
                            {

                                using (DataTable iDT = new DataTable())
                                {
                                    ItemsForSale_dataGridView.Columns[4].Name = "Serial";
                                    iDA.Fill(iDT);
                                    ItemsForSale_dataGridView.DataSource = iDT;
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    TextWriter tw = new StreamWriter("ex.txt");
                    tw.WriteLine(ex);
                    tw.Close();
                }
 
Last edited:

PDS8475

Active member
Joined
Jun 25, 2019
Messages
27
Programming Experience
Beginner
Just incase my problem isn't clear, here are two screenshots, The first being unfiltered and the second filtering by Name.
UnFiltered.jpg


Filtered.jpg
 

PDS8475

Active member
Joined
Jun 25, 2019
Messages
27
Programming Experience
Beginner
Loop through the columns of DataGridView after databinding and change each columns HeaderText.
Thanks for that but I must be doing something wrong.
C#:
{

                      
                        using (OleDbCommand icommand = new OleDbCommand("Select * FROM ItemsForSale INNER JOIN Engineering ON Engineering.Serial = ItemsForSale.Serial WHERE Engineering.Name = @s", iconnection))
                        {

                            icommand.CommandType = CommandType.Text;
                            icommand.Parameters.AddWithValue(@s, Details1_textBox.Text);
                            using (OleDbDataAdapter iDA = new OleDbDataAdapter(icommand))
                            {

                                using (DataTable iDT = new DataTable())
                                {
                                    
                                        
                                        iDA.Fill(iDT);
                                    
                                    for (int i = 0; i < ItemsForSale_dataGridView.Columns.Count; i++)
                                    {
                                        ItemsForSale_dataGridView.Columns["ItemsForSale.Type"].Name = "Type";
                                        ItemsForSale_dataGridView.Columns[2].Name = "Make";
                                        ItemsForSale_dataGridView.Columns[3].Name = "Model";
                                        ItemsForSale_dataGridView.Columns[4].Name = "Serial";
                                        ItemsForSale_dataGridView.DataSource = iDT;
                                    }
                                }
                            }
                        }
                    }
If I call the column by number and rename it nothing happens, If I call the column by name I get a Null Reference Exception.
if I have the line ItemsForSale_dataGridView.DataSource = iDT; before the loop nothing happens, also nothing happens with it after the loop.
 

JohnH

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
540
Location
Norway
Programming Experience
10+
I would just do a regex replace after datasource is set:
C#:
ItemsForSale_dataGridView.DataSource = iDT;

foreach (DataGridViewColumn col in ItemsForSale_dataGridView.Columns) {
    col.HeaderText = Regex.Replace(col.HeaderText, @"^.*?\.", string.Empty);
}
 

PDS8475

Active member
Joined
Jun 25, 2019
Messages
27
Programming Experience
Beginner
I would just do a regex replace after datasource is set:
C#:
ItemsForSale_dataGridView.DataSource = iDT;

foreach (DataGridViewColumn col in ItemsForSale_dataGridView.Columns) {
    col.HeaderText = Regex.Replace(col.HeaderText, @"^.*\.", string.Empty);
}
Thank you
That works perfectly
 
Top Bottom