Solved

moving at Datatable into a grid

Posted on 2013-10-30
11
197 Views
Last Modified: 2013-11-01
Normally this works.. I can't see the problem with the code. But I have a count on the datatable which has 10 columns and 300+ rows.. Move it into the datagrid and redo the count and both coulmn & row = 0..

I was hoping a second pair of eyes would see it.

            cmd = New SqlCommand(CommandText)
            'cmd.CommandType = CommandType.StoredProcedure;
            ' Open connection to the database
            con = New SqlConnection(pblvar.pblSQLPath)
            con.Open()
            cmd.Connection = con
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dtAwards)
            Dim rowko2 As Integer = dtAwards.Rows.Count
            'BindingSource to sync DataTable and DataGridView
            Dim bSource As New BindingSource()
            'set the BindingSource DataSource
            bSource.DataSource = dtAwards
' counts columns = 10 and rows = 380
            Dim intcko2 As Integer = dtAwards.Columns.Count
            Dim intrko2 As Integer = dtAwards.Rows.Count

            'set the DataGridView DataSource
            dgMeritBadgetkn.DataSource = bSource
'counts columns = 0 and rows = 0
            Dim intcko3 As Integer = dgMeritBadgetkn.Columns.Count
            Dim intrko3 As Integer = dgMeritBadgetkn.Rows.Count

Open in new window

0
Comment
Question by:moosetracker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39617374
Is this winforms or ASP.NET? If ASP.NET, you will need to call Databind() after assigning the data source.


By the way, following portion of your code

            cmd = New SqlCommand(CommandText)
            'cmd.CommandType = CommandType.StoredProcedure;
            ' Open connection to the database
            con = New SqlConnection(pblvar.pblSQLPath)
            con.Open()
            cmd.Connection = con
            Dim da As New SqlDataAdapter(cmd)

Open in new window


can be simplified to

            Dim da As New SqlDataAdapter(CommandText, pblvar.pblSQLPath)

Open in new window

0
 
LVL 1

Author Comment

by:moosetracker
ID: 39617452
Sorry.. This is winforms..  I am trying to convert from logic that works for me in a C# winform to workable logic in a VB.net winform, if that is of any help..

thanks
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39617468
Does the grid show any rows?

Does changing the following line

dgMeritBadgetkn.DataSource = bSource

to

dgMeritBadgetkn.DataSource = dtAwards

make any difference?
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 1

Author Comment

by:moosetracker
ID: 39617589
I tried the

dgMeritBadgetkn.Datasource = dtAwards

Didn't change anything, but I was surprised it didn't blow up.. I would have thought I wouldn't do that extra step, if I didn't need it..

The grid shows no rows, it is totally being built programmically. Several versions ago, I know I could have tied the Datagridview directly to a table or sql command..  Seems like about 2 versions back, I could not anymore unless I tied it to a dataset.. Dislike intensly the dataset as it is not easy to change, and is not moble if you need to tie it to a different SQL database..  I normally have only one public variable that I change to roll out a different server through the program..

The code I am changing is an older VB code program that did have a dataset, and a datagrid showing the columns.. But moving it to a different server and I can't even get into the dataset code to fix it..  So I am trying to bring it up to specs with newer programs of mine (written in C#) that programmically create the data for grids with the server pointing to a public variable, as well as programmically changing the datasource for crystal, and of course all sql logic in the code all using the one public variable of the server connection.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39617659
Does not make sense that the grid does not show any rows. Is the behaviour same if you add another temporary grid to the form and bind the datatable to it?
0
 
LVL 1

Author Comment

by:moosetracker
ID: 39618096
Sure you can have a grid with no column setup and it will work when run.. The thing is the grid IS NOT bound to the data Table until run time..  My new VB attempt blows up when I run it, because the Binding at run time is not working, which is my problem.. The Grid gets zero columns & zero rows and program aborts..

I don't have a VB example to show you, as I now code in C#.. But I am now showing you an example of the same thing in a C# program.. Two pictures, one shows the form in design mode empty, then the other shows the form in Run mode set up..

The code that sets up the binding and the layout of the columns programically in C# is as follows.

        private void FillParticpantGrid()
        {
            DataTable dt = new DataTable();
            
            try
            {
                SqlConnection conn = new SqlConnection(pblvar.pblSQLPath);
                CommandText = "Select wb.PersonID, " +
                    " wb.lastname + ', ' + wb.Firstname as FullName, sp.ShirtSize as chkShirtSize " +
                    " from vWoodBadger wb " +
                    " inner join vParticipant sp on sp.personID = wb.personID " +
                    " where sp.CourseKey = " + pblvar.SelectedCourseKey +
                    " and sp.Accepted = 1" + 
                    " Order by wb.LastName, wb.FirstName ";
                cmd = new SqlCommand(CommandText);
                //cmd.CommandType = CommandType.StoredProcedure;


                // Open connection to the database
                con = new SqlConnection(pblvar.pblSQLPath);
                con.Open();

                cmd.Connection = con;

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(dt);
                int rowko2 = dt.Rows.Count;

                //BindingSource to sync DataTable and DataGridView
                BindingSource bSource = new BindingSource();

                //set the BindingSource DataSource
                bSource.DataSource = dt;
                //set the DataGridView DataSource
                dgParticipants.DataSource = bSource;

                // Resize the DataGridView columns to fit the newly loaded content.
                dgParticipants.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
 
                dgParticipants.Columns["PersonID"].Visible = false;

                DataGridViewColumn column = dgParticipants.Columns["FullName"];
                column.ReadOnly = true;
                column = dgParticipants.Columns["chkShirtSize"];
                column.ReadOnly = true;
                column.Width = 100;

                DataGridViewComboBoxColumn comboboxColumn = new DataGridViewComboBoxColumn();
                comboboxColumn.HeaderText = "ShirtSize";
                comboboxColumn.DropDownWidth = 50;
                comboboxColumn.Width = 100;
                comboboxColumn.MaxDropDownItems = 5;
                comboboxColumn.FlatStyle = FlatStyle.Flat;
                comboboxColumn.DefaultCellStyle.BackColor = Color.PaleGoldenrod;
                comboboxColumn.ReadOnly = false;

                dgParticipants.TabStop = true;
                dgParticipants.Focus();
                dgParticipants.Columns.Insert(3, comboboxColumn);
                Startup = false;

                comboboxColumn.Items.Add("--Select--");


                CommandText = "Select distinct Description as ShirtSize" +
                              " from SmallFillLists " +
                              " where ListType = 'ShirtSz' ";

                con = new SqlConnection(pblvar.pblSQLPath);
                con.Open();
                cmd = new SqlCommand(CommandText);
                cmd.Connection = con;
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    comboboxColumn.Items.Add(rdr["ShirtSize"]);
                }
                int rowcount = -1;
                int introw = 0;
                    foreach (DataGridViewRow row in dgParticipants.Rows)
                    {
                        rowcount = rowcount + 1;
                        badPartipantShirts[rowcount] = row.Cells[2].Value.ToString() ;

                        if (row.Cells[2].Value.ToString().Trim() != "")
                            row.Cells[3].Value = row.Cells[2].Value.ToString();
                        else
                            row.Cells[3].Value = "--Select--";

                        if (row.Cells[3].Value.ToString() != "--Select--")
                        {
                            row.Cells[2].Value = "";
                        }
                        ////
                    }


                lblWarning.Text = "";
            }
            catch (Exception exp)
            {
               if (exp.Message.IndexOf("SetCurrentCellAddressCore")  == -1)
                {
                    throw; 
               }
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }

                if (con.State == ConnectionState.Open)
                    con.Close();
            }


        }

Open in new window


form in design modeform at run time
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39618118
When I bind my grids to datatables at runtime, I see the grids populated with rows and columns. If you comment out any code after setting the data source and then run the program, do you still see an empty datagrid?
0
 
LVL 1

Author Comment

by:moosetracker
ID: 39618159
Ahh yes.. at runtime you will see the rows & columns, as shown in my c# program.. But the VB program just aborts at the point where I try to adjust the first column.. That is because although the DataTable shows 10 columns (one of which is PersonID, when I bind the DataTable to the datagrid the DataGridViewColumn.Columns.Count = 0 so the column PersonID is not found and the code blows up.

At this point..

            
            Dim column As DataGridViewColumn = dgMeritBadgetkn.Columns("PersonID")
            column.Visible = False

Open in new window



(by the way, in your first comment, you showed me how to simplify.. (Don't know why I didn't see that when I first read the comment.. But, I wanted to thank you for that.. It does look a lot simpler..

This seems like a very weird strange error.. Huh.???  Code looks like it should work, yet it is not..  I  am debating if I should create a new project with a new form and see if it works, rather then trying to change this old code.. Maybe something went bonkers when it got upgraded to a newer VS version, or it's somehow corrupted in some other way.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39618165
So if you remove the code that changes the column properties etc then the grid shows rows correctly?

Yeah try creating a dummy project with nothing else but a form with grid and this code to populate it.
0
 
LVL 1

Author Comment

by:moosetracker
ID: 39618173
I didn't try that, but I don't think so.. in the Datatable the dtAwards.Rows.Count = 385, but once bound the dgMeritBadgetkn.Rows.Count  = 0..   Neither the Columns or Rows from the datatable move into the datagrid..

Ok.. Let me see what it does in a dummy project..
0
 
LVL 1

Author Closing Comment

by:moosetracker
ID: 39618188
Yeah.. The new dummy project worked.. I basically created the form, plopped the datagrid on it then cut and pasted the vb code from the other project in, and it worked..   Something corrupt in the old project.. Thanks for being my sounding board..
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question