Changing headertext of datagridview by using Sqlquery

ankit goel 746 Reputation points
2022-09-21T08:14:07.767+00:00

I am working on a .NET project where I am trying to change the column heading names in datagridview by using sqlquery but for unknown reason I am unable to do so . However the problem has been countered by using below code
dataGridView.Columns[0].HeaderText = "Last_Sale_Date"
dataGridView.Columns[1].HeaderText = "last sale to party"

but I still don't know the exact reason why the sqlquery is not working properly .
Can anyone help me out in this matter

string query = "SELECT $_LastSaleDate AS 'Last_Sale_Date', $_LastSaleParty AS 'last sale to party', $_LastSalePrice AS 'last sold price', $Name AS 'name of party', $_ClosingBalance AS 'closing balance' FROM ItemMovementAnalysis WHERE ($_ClosingBalance = 1511) ";
using (OdbcDataAdapter dadapter = new OdbcDataAdapter(query, con))
{
DataTable table = new DataTable();
dadapter.Fill(table);
this.dataGridView1.DataSource = table;
}

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,438 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,350 questions
{count} votes

Accepted answer
  1. Karen Payne MVP 35,196 Reputation points
    2022-09-21T13:22:21.673+00:00

    Okay I see the database is MS-Access, no matter, the following is a short mock-up.

    Full source

    The following simulates getting data

    public class Mocked  
    {  
        public static DataTable GetData()  
        {  
            DataTable table = new DataTable();  
      
            table.Columns.Add("$_LastSaleDate", typeof(DateTime));  
            table.Columns.Add("$_LastSalePrice", typeof(decimal));  
            table.Columns.Add("$Name", typeof(string));  
      
      
            table.Rows.Add(  
                new DateTime(2022, 9, 12),  
                124.89m,  
                "Jim's");  
      
            return table;  
        }  
    }  
    

    Then several extension methods, one is for version 1 of what is to follow and the second extension is optional, it expands column widths

    public static class Extensions  
    {  
        [DebuggerStepThrough]  
        public static string SplitCamelCase(this string sender) =>  
            string.Join(" ", Regex.Matches(sender, @"([A-Z][a-z]+)")  
                .Select(m => m.Value));  
      
        [DebuggerStepThrough]  
        public static void ExpandColumns(this DataGridView source, int rightPad = 0)  
        {  
            for (int index = 0; index <= source.Columns.Count - 1; index++)  
            {  
                int columnWidth = source.Columns[index].Width;  
                source.Columns[index].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;  
                source.Columns[index].Width = columnWidth + rightPad;  
            }  
        }  
    }  
    

    The following is for the second DataGridView

    public class LookupReferences  
    {  
        [DebuggerStepThrough]  
        public static Dictionary<string, string> Columns() =>  
            new Dictionary<string, string>(StringComparer.InvariantCultureIgnoreCase)  
            {  
                { "$_LastSaleDate", "last sale date" },  
                { "$_LastSaleParty", "last sale to party" },  
                { "$_LastSalePrice", "last sold price" },  
                { "$Name", "name of party" },  
                { "$_ClosingBalance", "closing balance" }  
            };  
    }  
    

    Form code

    public partial class Form1 : Form  
    {  
        public Form1()  
        {  
            InitializeComponent();  
            Shown += OnShown;  
      
        }  
      
        private void OnShown(object? sender, EventArgs e)  
        {  
      
            dataGridView1.DataSource = Mocked.GetData();  
            foreach (DataGridViewColumn column in dataGridView1.Columns)  
            {  
                column.HeaderText = column.HeaderText.SplitCamelCase().ToLower();  
            }  
            dataGridView1.ExpandColumns(10);  
      
      
            var references = LookupReferences.Columns();  
            dataGridView2.DataSource = Mocked.GetData();  
            foreach (DataGridViewColumn column in dataGridView2.Columns)  
            {  
                if (references.ContainsKey(column.HeaderText))  
                {  
                    column.HeaderText = references[column.HeaderText];  
                }  
            }  
            dataGridView2.ExpandColumns(10);  
        }  
    }  
    

    243458-figure1.png


1 additional answer

Sort by: Most helpful
  1. Jack J Jun 24,296 Reputation points Microsoft Vendor
    2022-09-22T05:33:07.207+00:00

    @ankit goel , Based on my test, your code works well for me to show the Correct columns in the datagirdview. Your problem may be related to your database.

    However, I think you still could use dataGridView1.Columns[i].HeaderText to change the columns.

    Code:

     private void button1_Click(object sender, EventArgs e)  
            {  
                string[] selectedColumns = new[] { "Co1", "Col2", "Col3", "Col4", "Col5" };  
                for (int i = 0; i < dataGridView1.Columns.Count; i++)  
                {  
                    dataGridView1.Columns[i].HeaderText = selectedColumns[i];  
                }  
            }  
    

    Result:

    243791-5.gif

    Hope this could help you.

    Best Regards,
    Jack


    If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.