none
Binding LINQ-to-SQL to DataGridView just doesn't work RRS feed

  • Question

  • I'm trying to setup an application that pulls data out of a database (Microsoft SQL Server 2008) into a DataGridView.

    This is .NET 3.5.

    I have created all the necessary entities, and they all work (i.e. I can query the DB through the entities I've created and then foreach through them and I get the appropriate results printed out).

    I have created a Windows Forms Application with a DataGridView, a BindingSource, and of course the necessary class (which is subclassed from DataContext) (this class is called "Inventory").

    The relevant lines of code are here.

    In the form's class scope:

    private BindingSource bs;
    
    private DataGridView displaytable;
    
    internal Inventory db;
    
    

    In the form's constructor:

    bs=new BindingSource();
    
    
    
    displaytable=new DataGridView();
    
    displaytable.DataSource=bs;
    
    displaytable.AutoGenerateColumns=true;
    
    
    
    db=new Inventory("<SQL CONNECTION STRING>");

    In the form's Load event handler:

    bs.DataSource=db.Locations;

    When I run the application, the DataGridView just sits there, empty.

    If I change the code to point at a random list:

    bs.DataSource=new String [] {"hello","good-bye"};

    It works as expected.

    Help?

    BTW: If I disconnect the BindingSource from the DataGridView, and try and manually pull database rows out of the BindingSource, it works.

    Saturday, September 4, 2010 8:08 AM

All replies

  • hi

    change your code to become like this, bind the datagrid after you bind the binding source

    bs.DataSource=db.Locations;

     displaytable.DataSource=bs;

    or refresh the grid

    regards

    Saturday, September 4, 2010 11:29 AM
  • hi

    change your code to become like this, bind the datagrid after you bind the binding source

    bs.DataSource=db.Locations;

     displaytable.DataSource=bs;

    or refresh the grid

    regards


    Thanks but neither of those suggestions change anything.

    DataGridView still totally blank.

    Note: Using .NET 4.0 changes nothing.

    Saturday, September 4, 2010 8:33 PM
  • Hi

    strange problem

    you need to try to debug your code, 

    in case if you don't know how

    1- put the cursor on the suspected line and then press F9 then click F5 to run using the debugger

    2- when the code execution reaches your suspected code it will jump to show you and you can run your program step by step using F11

    you need to know if the db.Locations is returning data or not, you can know this by looking to the grid Columns, if there are some columns in Grid without rows then you table Location doesn't contain any data and the code is working fine

    also try to find how many records inside db.Locations by right click on db.locations choose add to watch and in the watch window retype the db.locations to be db.Locations.Count() 

    hope this help you

    regards

    • Proposed as answer by liurong luo Wednesday, September 8, 2010 8:28 AM
    • Unproposed as answer by Spike15 Thursday, September 9, 2010 10:19 PM
    Sunday, September 5, 2010 10:45 AM
  • Hi

    strange problem

    you need to try to debug your code, 

    in case if you don't know how

    1- put the cursor on the suspected line and then press F9 then click F5 to run using the debugger

    2- when the code execution reaches your suspected code it will jump to show you and you can run your program step by step using F11

    you need to know if the db.Locations is returning data or not, you can know this by looking to the grid Columns, if there are some columns in Grid without rows then you table Location doesn't contain any data and the code is working fine

    also try to find how many records inside db.Locations by right click on db.locations choose add to watch and in the watch window retype the db.locations to be db.Locations.Count() 

    hope this help you

    regards


    I don't know why everyone assumes that everyone is using Visual Studio -- I am not.

    Also, I posted in the OP, in "BTW", that db.Locations and the BindingSource are both [apparently] working properly, since I can pull the expected db.Locations data out of the BindingSource manually, it's just that the DataGridView won't do it.

    Monday, September 6, 2010 1:00 AM
  • Not using VS, well, hmm people asume, common mistake, VS is the de facto platform for this.

    Onto your problem, there is nothing in your datagrid because there is nothing in your bindingsource, simple as that.

    Unless you want something from the db linq wont call the db so you need to force this onto a query.

    Try binding the datagridview to a simple queryresult.

    Let us know how that one goes.

    Regards

    Tuesday, September 7, 2010 12:26 PM
  • Serguey123's right. No matter what IDE you used, Can you please try to debug and check there are any data in your datasource. More spcifically, you could go and check your db.Locations. What the output of db.Locations.Count()?

    Thanks a lot!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Wednesday, September 8, 2010 8:26 AM
  • Serguey123's right. No matter what IDE you used, Can you please try to debug and check there are any data in your datasource. More spcifically, you could go and check your db.Locations. What the output of db.Locations.Count()?

    Thanks a lot!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!


    Does no one actually read what I post?

    I have said TWICE that both db.Locations AND the BindingSource are working -- I can pull values out of them both.

    Wednesday, September 8, 2010 7:46 PM
  • Yes I read it, and yes the data is there, but linq won't pull it for you because it doesn't see the need to do so.  Use a query to load the data to an object and bind that to the datasource, also consider forcing execution with a ToList call.

    The wheater is bad here so in all likeness I won't be able to post until tomorrow so I'm making this short.

    Regards.

    PS: I don't care much for caps.

    Wednesday, September 8, 2010 8:03 PM
  • Yes I read it, and yes the data is there, but linq won't pull it for you because it doesn't see the need to do so.  Use a query to load the data to an object and bind that to the datasource, also consider forcing execution with a ToList call.

    The wheater is bad here so in all likeness I won't be able to post until tomorrow so I'm making this short.

    Regards.

    PS: I don't care much for caps.


    using System;
    using System.Linq;
    
    public static class LINQToSQLTest {
    
    
    	public static void Main () {
    	
    		Inventory db=new Inventory(@"");
    		
    		Console.WriteLine(db.Locations.Count());
    	
    	}
    
    
    }
    

    To humour everyone.

    I have blanked out the connection string since I'm using a test db and have the password etc. in there in plaintext.

    The output of this code is "14".

    using System;
    using System.Linq;
    using System.Data.Linq;
    using System.Windows.Forms;
    
    
    public static class LINQToSQLTest {
    
    
    	public static void Main () {
    	
    		Inventory db=new Inventory(@"");
    		
    		BindingSource bs=new BindingSource();
    		
    		bs.DataSource=
    			from x in db.Locations
    			select x;
    		
    		for (Int32 i=0;i<bs.Count;i++) {
    		
    			Console.WriteLine(((Location)bs.Current).LocName);
    			
    			bs.MoveNext();
    		
    		}
    	
    	}
    
    
    }
    

    The output of this code is:

    CRD Headquarters
    Juan de Fuca Building Inspection
    Pender Building Inspection
    Salt Spring Island Building Inspection
    CRD Animal Shelter
    Bylaw Services
    Hartland Landfill
    Macaulay Point
    Saanich Peninsula Wastewater Treatment Plant
    Water Headquarters
    Panorama Recreation Centre
    Parks Headquarters
    Salt Spring Island Parks & Recreation
    Sooke Electoral Area Parks & Recreation

    Which is every item in the Locations table.

    Once again the connection string has dutifully been blanked out.

    using System;
    using System.Linq;
    using System.Windows.Forms;
    using System.Drawing;
    
    
    public class LINQToSQLTest : Form {
    
    
    	public static void Main () {
    	
    		Application.Run(new LINQToSQLTest());
    	
    	}
    	
    	
    	private LINQToSQLTest () {
    	
    		Size size=new Size(800,600);
    	
    		ClientSize=size;
    		Size thesize=new Size(Size.Width,Size.Height);
    		MaximumSize=thesize;
    		MinimumSize=thesize;
    		
    		Inventory db=new Inventory(@"");
    		
    		BindingSource bs=new BindingSource();
    		
    		DataGridView testgrid=new DataGridView();
    		testgrid.Size=size;
    		testgrid.Location=new Point(0,0);
    		testgrid.AutoGenerateColumns=true;
    		
    		Controls.Add(testgrid);
    		
    		bs.DataSource=db.Locations;
    		
    		testgrid.DataSource=bs;
    	
    	}
    
    
    }
    

    Produces:

    http://rleahy.ca/Images/linqtosqldatagridviewfailure.jpg

    using System;
    using System.Linq;
    using System.Windows.Forms;
    using System.Drawing;
    
    
    public class LINQToSQLTest : Form {
    
    
    	public static void Main () {
    	
    		Application.Run(new LINQToSQLTest());
    	
    	}
    	
    	
    	private LINQToSQLTest () {
    	
    		Size size=new Size(800,600);
    	
    		ClientSize=size;
    		Size thesize=new Size(Size.Width,Size.Height);
    		MaximumSize=thesize;
    		MinimumSize=thesize;
    		
    		Inventory db=new Inventory(@"");
    		
    		BindingSource bs=new BindingSource();
    		
    		DataGridView testgrid=new DataGridView();
    		testgrid.Size=size;
    		testgrid.Location=new Point(0,0);
    		testgrid.AutoGenerateColumns=true;
    		
    		Controls.Add(testgrid);
    		
    		bs.DataSource=(
    			from x in db.Locations
    			select x
    		).ToList();
    		
    		testgrid.DataSource=bs;
    	
    	}
    
    
    }
    

    Produces the same as the immediately preceding.

    Thursday, September 9, 2010 5:39 PM
  • The problem seems to be with the DGV, can you check that the data is properly loaded into the list?

    Alternatively try this, instead try binding directly to the DGV and see what happens

    Thanks

    Thursday, September 9, 2010 6:03 PM
  • The problem seems to be with the DGV, can you check that the data is properly loaded into the list?

    Alternatively try this, instead try binding directly to the DGV and see what happens

    Thanks


    using System;
    using System.Linq;
    using System.Windows.Forms;
    using System.Drawing;
    
    
    public class LINQToSQLTest : Form {
    
    
    	public static void Main () {
    	
    		Application.Run(new LINQToSQLTest());
    	
    	}
    	
    	
    	private LINQToSQLTest () {
    	
    		Size size=new Size(800,600);
    	
    		ClientSize=size;
    		Size thesize=new Size(Size.Width,Size.Height);
    		MaximumSize=thesize;
    		MinimumSize=thesize;
    		
    		Inventory db=new Inventory(@"");
    		
    		DataGridView testgrid=new DataGridView();
    		testgrid.Size=size;
    		testgrid.Location=new Point(0,0);
    		testgrid.AutoGenerateColumns=true;
    		
    		Controls.Add(testgrid);
    		
    		testgrid.DataSource=db.Locations;
    		
    		MessageBox.Show(testgrid.Rows.Count.ToString());
    	
    	}
    
    
    }
    

    Produces:

    • An empty DataGridView
    • A messagebox containing "0"

    If I change the line which sets the DGV's DataSource to

    testgrid.DataSource=(
    	from x in db.Locations
    	select x
    ).ToList();
    
    Nothing changes.
    Thursday, September 9, 2010 8:24 PM
  • For interest's sake, this code works as expected:

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Windows.Forms;
    using System.Drawing;
    
    
    public class LINQToSQLTest : Form {
    
    
    	public static void Main () {
    	
    		Application.Run(new LINQToSQLTest());
    	
    	}
    	
    	
    	private LINQToSQLTest () {
    	
    		Size size=new Size(800,600);
    	
    		ClientSize=size;
    		Size thesize=new Size(Size.Width,Size.Height);
    		MaximumSize=thesize;
    		MinimumSize=thesize;
    		
    		DataSet ds=new DataSet();
    		
    		DataGridView testgrid=new DataGridView();
    		testgrid.Size=size;
    		testgrid.Location=new Point(0,0);
    		testgrid.AutoGenerateColumns=true;
    		
    		Controls.Add(testgrid);
    		
    		using (SqlConnection conn=new SqlConnection(@"")) {
    		
    			conn.Open();
    			
    			(new SqlDataAdapter(
    				new SqlCommand(
    					@"
    						SELECT *
    						FROM Locations
    					",
    					conn
    				)
    			)).Fill(
    				ds,
    				"Locations"
    			);
    		
    		}
    		
    		testgrid.DataSource=ds;
    		testgrid.DataMember="Locations";
    		
    		MessageBox.Show(testgrid.Rows.Count.ToString());
    	
    	}
    
    
    }
    
    Thursday, September 9, 2010 8:34 PM
  • Great that this worked, at least now you have a workaroud, however I'm curious as of why this happens, I assume this can be replicated so, normally I'll said that you should send a sample to the msft team but they are in no way obliged to help with anything that is not Microsoft product (you said you were not using VS so).

    I have used lines of code not unlike this in VS ( I have VS 2010 ultimate ed) and it worked without a hitch so far.

    1. I can't promise anything but if you give me the spec of what you are using I'll try to replicate this at home
    2. Would you mind terribly doing a few more tests for me?

    Can you load the query result on a list, ex:

    List<Locations> test=(from x in db.Locations  select x).ToList();

    and check if the list constain the proper elements so as to ascertain that linq is pulling the data

    If so then bind the list to the DGV and see what happens.

    Thanks

    Friday, September 10, 2010 1:48 PM
  • Great that this worked, at least now you have a workaroud, however I'm curious as of why this happens, I assume this can be replicated so, normally I'll said that you should send a sample to the msft team but they are in no way obliged to help with anything that is not Microsoft product (you said you were not using VS so).

    I have used lines of code not unlike this in VS ( I have VS 2010 ultimate ed) and it worked without a hitch so far.

    1. I can't promise anything but if you give me the spec of what you are using I'll try to replicate this at home
    2. Would you mind terribly doing a few more tests for me?

    Can you load the query result on a list, ex:

    List<Locations> test=(from x in db.Locations  select x).ToList();

    and check if the list constain the proper elements so as to ascertain that linq is pulling the data

    If so then bind the list to the DGV and see what happens.

    Thanks


    I thought that Visual C#.NET itself, along with the .NET framework, were both Microsoft products?

    As for your suggestion:

    using System;
    using System.Linq;
    using System.Windows.Forms;
    using System.Drawing;
    using System.Collections.Generic;
    using System.Text;
    
    
    public class LINQToSQLTest : Form {
    
    
    	public static void Main () {
    	
    		Application.Run(new LINQToSQLTest());
    	
    	}
    	
    	
    	private LINQToSQLTest () {
    	
    		Inventory db=new Inventory(@"");
    	
    		Size size=new Size(800,600);
    	
    		ClientSize=size;
    		Size thesize=new Size(Size.Width,Size.Height);
    		MaximumSize=thesize;
    		MinimumSize=thesize;
    		
    		DataGridView testgrid=new DataGridView();
    		testgrid.Size=size;
    		testgrid.Location=new Point(0,0);
    		testgrid.AutoGenerateColumns=true;
    		
    		Controls.Add(testgrid);
    		
    		List<Location> test=(
    			from x in db.Locations
    			select x
    		).ToList();
    		
    		StringBuilder sb=new StringBuilder();
    		Boolean first=true;
    		foreach (Location x in test) {
    		
    			if (first) {
    			
    				first=false;
    			
    			} else {
    			
    				sb.Append("\n");
    			
    			}
    		
    			sb.Append(x.LocName);
    		
    		}
    		MessageBox.Show(sb.ToString());
    		
    		testgrid.DataSource=test;
    	
    	}
    
    
    }
    
    The above produces a message box with all the expected elements from Locations, and then an empty DataGridView.
    Friday, September 10, 2010 4:06 PM
  • Sorry I was under the impresion you used Mono or something like that (which is not a MS product at all)

    So the problem is the DGV, you might need to manually load the elements.  Also try putting autogenerate=false, creating the columns manually and them binding it (something like this happened to me a while ago, but with entities)

    You can try to put a ticket in Microsoft.Connect to see if they can/will solve this.

    They will need a small demo for replication purposes.

    However as stated before if you tell me what you are using I in my spare time might try to replicate this on my own and see if I can help you further.  Anything else would be guesswork and I dislike doing that as it often make me look like a fool (I don't want people noticing that I am)

    Regards

    Friday, September 10, 2010 6:34 PM
  • Sorry I was under the impresion you used Mono or something like that (which is not a MS product at all)

    So the problem is the DGV, you might need to manually load the elements.  Also try putting autogenerate=false, creating the columns manually and them binding it (something like this happened to me a while ago, but with entities)

    You can try to put a ticket in Microsoft.Connect to see if they can/will solve this.

    They will need a small demo for replication purposes.

    However as stated before if you tell me what you are using I in my spare time might try to replicate this on my own and see if I can help you further.  Anything else would be guesswork and I dislike doing that as it often make me look like a fool (I don't want people noticing that I am)

    Regards


    I'm using NotePad++ and csc.exe to make and compile my applications.

    I personally don't like VS.  I don't like how it writes so much boilerplate code for you (I prefer to have the control), and I don't like how it forces formatting on me that I couldn't -- at least that I could figure out in VS2008 -- change.

    I'll try your suggestion about the DGV.  It's especially confusing to me because everything I could find on-line about LINQ-to-SQL populating DGVs said that this should work...

    Friday, September 10, 2010 8:58 PM
  • Yes, it should, as a matter of fact in my machine with vs it works, so anyways, what framework are you targetting and what version of the csc.exe are you using?

    Thanks

    PS: Have you thought that perhaps some part of that boilerplate code is responsable for making this work?

    Monday, September 13, 2010 11:55 AM
  • Yes, it should, as a matter of fact in my machine with vs it works, so anyways, what framework are you targetting and what version of the csc.exe are you using?

    Thanks

    PS: Have you thought that perhaps some part of that boilerplate code is responsable for making this work?


    It doesn't even work when I do it in VS2008.

    Microsoft (R) Visual C# 2008 Compiler version 3.5.30729.4926
    for Microsoft (R) .NET Framework version 3.5

    is the csc.exe that I'm using.

    Monday, September 13, 2010 4:26 PM
  • Hmm, could you create a small demo project and mail it to me, I'm fairly busy but in the weekend I'll try to check it, it just work on my dev machine.

    Thanks

    My mail asdasd<no spam>asd@gmx.com

    //Remove the no spam part

    Also, I think I remember some problems with a version of framework 3.5, it was an update.  All this is bizarre.

    Regards

    Tuesday, September 14, 2010 1:09 PM
  • Hello Spike15,

    I'm writing to check the status of this thread. Please let us know if you have more concern on the issue. We'd be more than happy to help.

    Thanks a lot!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Friday, September 24, 2010 10:09 AM
  • Same problem here. I too can get the data programmatically but the binding gives me a blank grid. Anyone figured this out? 
    Sunday, October 10, 2010 1:50 AM