question

ankitgoel-6643 avatar image
0 Votes"
ankitgoel-6643 asked ankitgoel-6643 commented

Changing headertext of datagridview by using Sqlquery

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;
}

dotnet-csharpdotnet-adonet
· 14
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ankitgoel-6643, Welcome to Microsoft Q&A, based on my test, the code works for me. Could you show your current datagirdview picture after loading the database table? Or did you get some errors when you use the above code?

0 Votes 0 ·

243407-micro.png




0 Votes 0 ·
micro.png (256.2 KiB)

Sir @JackJJun-MSFT, I didn't getting any error .

0 Votes 0 ·
Show more comments

What is the database e.g. SQL-Server, Oracle, MySql etc ?

0 Votes 0 ·

I exactly don't know what type of database it is . but i am sharing you a link and my connection string . Please its my request that you please go through this link once because i am a fresher .
https://help.tallysolutions.com/article/DeveloperReference/integration-capabilities/using_odbc_interface.htm

connection String
DRIVER=Tally ODBC Driver;DSN = TallyODBC_9000; SERVER = (local); PORT = 9000

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered karenpayneoregon commented

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



figure1.png (7.9 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@karenpayneoregon, thanks for giving time to my question. I am really thankful to u. I have some questions can you please sort it out also

1) the method Mocked.GetData(), can i used my odbcadapter code here instead of hardcoding the column names and data inside the row in it and return the datatable from it . and if i use my code , does the lines
table.Columns.Add("$_LastSaleDate", typeof(DateTime));
table.Columns.Add("$_LastSalePrice", typeof(decimal));
table.Columns.Add("$Name", typeof(string));

is the above three lines is mandatory.


2) "Okay I see the database is MS-Access ", from the last four months i am struggling to find out which type of database is it, how to access its settings and how to assign a ip address to it so that it can be accessible from internet as upto now i am using localhost in its querystring . Can you please guide me through all this.

0 Votes 0 ·

The mocked data was only used because I don't have your database so ignore the mocked code.

0 Votes 0 ·
JackJJun-MSFT avatar image
0 Votes"
JackJJun-MSFT answered ankitgoel-6643 commented

@ankitgoel-6643, 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.


5.gif (26.5 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

yes @JackJJun-MSFT , may be my problem has something to do with the database or the driver I am using .

0 Votes 0 ·

@ankitgoel-6643, Have you solved your problem? If not, Could you consider that use the sql server database?

0 Votes 0 ·
ankitgoel-6643 avatar image ankitgoel-6643 JackJJun-MSFT ·

No, The only option is to use the database and driver provided by the application .

0 Votes 0 ·
JackJJun-MSFT avatar image JackJJun-MSFT ankitgoel-6643 ·

@ankitgoel-6643, is any update?

0 Votes 0 ·
ankitgoel-6643 avatar image ankitgoel-6643 JackJJun-MSFT ·

My original question about "the exact reason why the sqlquery is not working properly" is still unsolved but I think that I should move forward with the other way to solve the same purpose. I thank you @JackJJun-MSFT and @karenpayneoregon for all your efforts .


0 Votes 0 ·