13 Replies - 21829 Views - Last Post: 09 August 2012 - 01:57 AM Rate Topic: -----

#1 Indrajitdasgupta  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 27-December 10

data grid view in C# windows application

Posted 07 August 2012 - 11:16 PM

Hi All,
I am using DataGridView at runtime in C# windows form,
Problem is my Data Grid View should displaying data like below

rack   1    2   3   4   5   6
A1     20  30      40      20
A2     10      20      30  12
A3         24      22      15
B1     12  34  22      67     
B2     11          22      14
B3         24      22      15
C1         14      15      16
C2     34  55          35  25   




now the DataGridView should display like below


     rack   1   2   3   4   5   6
     A1     20  30      40      20
 A   A2     10      20      30  12
     A3         24      22      15

     B1     12  34  22      67     
 B   B2     11          22      14
     B3         24      22      15

     C1         14      15      16
 C   C2     34  55          35  25  



Is This A Good Question/Topic? 0
  • +

Replies To: data grid view in C# windows application

#2 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3423
  • View blog
  • Posts: 10,544
  • Joined: 05-May 12

Re: data grid view in C# windows application

Posted 07 August 2012 - 11:48 PM

How are you populating the DataGridView? Are you using a SQL query and assigning the resulting dataset as a datasource, or are you adding columns and rows manually.
Was This Post Helpful? 0
  • +
  • -

#3 Indrajitdasgupta  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 27-December 10

Re: data grid view in C# windows application

Posted 07 August 2012 - 11:59 PM

I am using SQL Query (Store Procedure) and assigning that to Dataset

View PostSkydiver, on 07 August 2012 - 11:48 PM, said:

How are you populating the DataGridView? Are you using a SQL query and assigning the resulting dataset as a datasource, or are you adding columns and rows manually.


I am using a SQL query (Store Procedure) and assigning the resulting dataset as a datasource
Below my code

public partial class Form1 : Form
    {
        DataGridView dataGrid;
        DataSet dataSet;        
        OleDbDataAdapter da;       
        private OleDbConnection conn = new OleDbConnection();        
        private string conStr = "Provider=SQLOLEDB;Data Source=SERVICE\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=SSPI";
        public Form1()
        {
            InitializeComponent();
            conn = new OleDbConnection(conStr);
            conn.Open();
            InitializeComponent();
            //CreateGridAndButton();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string str = "SP_TestForAccess";
            OleDbCommand cmd = new OleDbCommand();
            cmd = new OleDbCommand(str, conn);
            dataGrid=new DataGridView();
            da = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            dataSet = new DataSet();
            da.SelectCommand = cmd;
            //dt.Load(dr);
            //dataSet.Tables.Add(dt);
            da.Fill(dataSet, "tbl_main");
            this.Controls.Add(dataGrid);
            dataGrid.Size = new System.Drawing.Size(750, 250);
            dataGrid.DataSource = dataSet.Tables[0];            
        }

        
    }


Was This Post Helpful? 0
  • +
  • -

#4 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3423
  • View blog
  • Posts: 10,544
  • Joined: 05-May 12

Re: data grid view in C# windows application

Posted 08 August 2012 - 12:15 AM

Can you insert this line of code between line 29 and 30:
MessageBox.Show(dataSet.Tables[0].Rows[1][0]);



What does the message box show when you run the code? "A"? "A A3"? or something else?
Was This Post Helpful? 0
  • +
  • -

#5 Indrajitdasgupta  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 27-December 10

Re: data grid view in C# windows application

Posted 08 August 2012 - 12:47 AM

View PostSkydiver, on 08 August 2012 - 12:15 AM, said:

Can you insert this line of code between line 29 and 30:
MessageBox.Show(dataSet.Tables[0].Rows[1][0]);



What does the message box show when you run the code? "A"? "A A3"? or something else?

Debug time it shows error below is the error

1) The best overloaded method match for'System.Windows.Forms.MessageBox.Show(String)'has some invalid arguments
2) Argument'1':cannot convert from 'object' to 'string'
Was This Post Helpful? 0
  • +
  • -

#6 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3423
  • View blog
  • Posts: 10,544
  • Joined: 05-May 12

Re: data grid view in C# windows application

Posted 08 August 2012 - 01:37 AM

Sorry, I've been doing WriteLine's the entire day, I forgot MessageBox.Show() wants a string. Try this:
MessageBox.Show(dataSet.Tables[0].Rows[1][0].ToString());



Basically, I'm curious to see if the 'A', 'B', and 'C' are being returned to you by your stored procedure.

If so, is there a flag you can pass to your stored procedure to tell it to stop. If not, it's just a matter of GIGO, and you'll just have to deal with it. I would try to set the first column as hidden when the data is loaded. Or even better, if you tell the DataGridView not to automatically generate columns, then you can set exactly what columns you want to be visible.
Was This Post Helpful? 1
  • +
  • -

#7 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 488
  • Joined: 13-June 12

Re: data grid view in C# windows application

Posted 08 August 2012 - 01:58 AM

Agree with SkyDiver. Manually set the columns in the DataGridView.

As a side note, shouldn't this:

Quote

OleDbCommand cmd = new OleDbCommand();
21
            cmd = new OleDbCommand(str, conn);



be this:

Quote

OleDbCommand cmd = new OleDbCommand(str, conn);



I'm sure you realized too (since you commented some lines out) but you won't need the DataTable dt.
Was This Post Helpful? 0
  • +
  • -

#8 Indrajitdasgupta  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 27-December 10

Re: data grid view in C# windows application

Posted 08 August 2012 - 02:12 AM

View PostSkydiver, on 08 August 2012 - 01:37 AM, said:

Sorry, I've been doing WriteLine's the entire day, I forgot MessageBox.Show() wants a string. Try this:
MessageBox.Show(dataSet.Tables[0].Rows[1][0].ToString());



Basically, I'm curious to see if the 'A', 'B', and 'C' are being returned to you by your stored procedure.

If so, is there a flag you can pass to your stored procedure to tell it to stop. If not, it's just a matter of GIGO, and you'll just have to deal with it. I would try to set the first column as hidden when the data is loaded. Or even better, if you tell the DataGridView not to automatically generate columns, then you can set exactly what columns you want to be visible.


it is showing a Popup box in that shows A2
My store procedure is below what should I change to get the require table
ALTER PROCEDURE [dbo].[SP_TestForAccess]
AS
BEGIN
DECLARE @query NVARCHAR(4000)
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols +',['+ cell +']','['+ cell +']') FROM (SELECT DISTINCT cell FROM TestForAccess)as T
PRINT @cols
SET @query ='select rack,'+@cols+' FROM (select rack,cell,qty FROM TestForAccess)AS D pivot(sum(qty) for cell in ('+@cols+')) as P;'
--EXEC SP_EXECUTESQL @query
PRINT @query
EXECUTE(@query)
END


Was This Post Helpful? 0
  • +
  • -

#9 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3423
  • View blog
  • Posts: 10,544
  • Joined: 05-May 12

Re: data grid view in C# windows application

Posted 08 August 2012 - 02:25 AM

I don't know... that's a SQL question, not a C# question, and that level of SQL is beyond me.
Was This Post Helpful? 0
  • +
  • -

#10 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 488
  • Joined: 13-June 12

Re: data grid view in C# windows application

Posted 08 August 2012 - 02:46 AM

I'm not sure, but I think if you change line 8 to
SET @query ='select rack,'+@cols+' FROM (select rack,cell,qty FROM TestForAccess)'
it might fix it. I've never used SQL PIVOT before.

This post has been edited by MrShoes: 08 August 2012 - 02:46 AM

Was This Post Helpful? 0
  • +
  • -

#11 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4408
  • View blog
  • Posts: 7,653
  • Joined: 08-June 10

Re: data grid view in C# windows application

Posted 08 August 2012 - 06:14 AM

Another side note: if that is Sql Server (and I believe it is, based on sp_executesql stored procedure), you should be using the native Sql Server client instead of an OleDb connection.
Was This Post Helpful? 0
  • +
  • -

#12 Indrajitdasgupta  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 27-December 10

Re: data grid view in C# windows application

Posted 08 August 2012 - 10:47 PM

View PostMrShoes, on 08 August 2012 - 02:46 AM, said:

I'm not sure, but I think if you change line 8 to
SET @query ='select rack,'+@cols+' FROM (select rack,cell,qty FROM TestForAccess)'
it might fix it. I've never used SQL PIVOT before.


Thanks for post
but it doesn't fix the problem
Was This Post Helpful? 0
  • +
  • -

#13 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 488
  • Joined: 13-June 12

Re: data grid view in C# windows application

Posted 09 August 2012 - 12:36 AM

OK, then as Skydiver mentioned:

Quote

if you tell the DataGridView not to automatically generate columns, then you can set exactly what columns you want to be visible.

You can achieve that with the following code:
dataGridView1.Columns[0].Visible = false;

Was This Post Helpful? 1
  • +
  • -

#14 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3423
  • View blog
  • Posts: 10,544
  • Joined: 05-May 12

Re: data grid view in C# windows application

Posted 09 August 2012 - 01:57 AM

View PostMrShoes, on 09 August 2012 - 12:36 AM, said:

OK, then as Skydiver mentioned:

Quote

if you tell the DataGridView not to automatically generate columns, then you can set exactly what columns you want to be visible.

You can achieve that with the following code:
dataGridView1.Columns[0].Visible = false;


Correct code. Incorrect quote. That code implements this:

Quote

I would try to set the first column as hidden when the data is loaded.

It's the quick and dirty way to get unblocked.

The harder way is to set AutoGenerateColumns to false, and add your columns one by one.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1