LINQ to SQL problem

[Matt_Hirst]

Am trying to use LIQ to populate dropdown lists and also a gridview, been following one of the many online tutorials and come up with the following:

 CCDataContext db = new CCDataContext();
                        string connectionString = System.Configuration.ConfigurationManager.AppSettings["ABPConnectionString1"];
                        db.Connection.ConnectionString = connectionString;
                        
                        var CCArea =
                        from c in db.tbl_CC_AreaInfos 
                        select c;
                        
                        GrdDynamic.DataSource = CCArea.ToList();
                        GrdDynamic.DataBind();

As this is just a test to see how to work with LINQ I am trying to return the entire table to a gridview, the problem lies in that nothing gets displayed to the gridview and I am not sure why?

I can see that CCArea contains 3012 rows, so it has the data, so it must be something to do with the next two lines.

Do I have to populate the datagrid with columns prior to binding the datasource or will the columns autogenerate?

Any other ideas?

Matt

 

[Matt_Hirst]

Thanks for that, sometimes the simplest things...

One other question:

I have a query that I need to perform that involves 6 tables, some have a single field linking then together whilst the main 2 have 4 fields linking them. Can you give me an example of how I create a query like this using LINQ?

Matt

 

[Matt_Hirst]

SQL that I ma trying to reproduce in LINQ:

SELECT tbl_CC_Times.txtTimeDescription
FROM (((tbl_CC_ZonePrices INNER JOIN tbl_CC_Surcharge ON (tbl_CC_ZonePrices.[Company] = tbl_CC_Surcharge.[CompanyCode]) AND (tbl_CC_ZonePrices.[DeliveryPeriod] = tbl_CC_Surcharge.[intDayCode]) AND (tbl_CC_ZonePrices.[Zone] = tbl_CC_Surcharge.[ZoneCode]) AND (tbl_CC_ZonePrices.[MinWeight] = tbl_CC_Surcharge.[MinWgt]) AND (tbl_CC_ZonePrices.[MaxWeight] = tbl_CC_Surcharge.[MaxWgt])) INNER JOIN tbl_CC_Times ON tbl_CC_Surcharge.[TimeCode] = tbl_CC_Times.[IDCode]) INNER JOIN tbl_CC_DayOfWeek ON tbl_CC_ZonePrices.[DeliveryPeriod] = tbl_CC_DayOfWeek.[IDCode]) INNER JOIN (tbl_CC_AreaCompanyZoneGeoChrge INNER JOIN tbl_CC_AreaInfo ON tbl_CC_AreaCompanyZoneGeoChrge.[intAreaCode] = tbl_CC_AreaInfo.[IDCode]) ON (tbl_CC_ZonePrices.[Company] = tbl_CC_AreaCompanyZoneGeoChrge.[intCompany]) AND (tbl_CC_ZonePrices.[Zone] = tbl_CC_AreaCompanyZoneGeoChrge.[intZone])
WHERE (((tbl_CC_AreaInfo.txtPostcodeSubArea)="S63") AND ((tbl_CC_DayOfWeek.txtDOWDescription)="NDay"))
GROUP BY tbl_CC_Times.txtTimeDescription
ORDER BY tbl_CC_Times.txtTimeDescription;

The linq code I have come up with:

var tests = from t in db.tbl_CC_Times
                         join s in db.tbl_CC_Surcharges on t.IDCode equals s.TimeCode
                         join zp in db.tbl_CC_ZonePrices on new { CompanyCode = s.CompanyCode, DayCode = s.intDayCode, ZoneCode = s.ZoneCode, MinWgt = s.MinWgt, MaxWgt = s.MaxWgt } equals new { CompanyCode = zp.Company, DayCode = zp.DeliveryPeriod, ZoneCode = zp.Zone, MinWgt = zp.MinWeight, MaxWgt = zp.MaxWeight }
                         join dow in db.tbl_CC_DayOfWeeks on zp.DeliveryPeriod equals dow.IDCode
                         join aczgc in db.tbl_CC_AreaCompanyZoneGeoChrges on new { zp.Company, zp.Zone } equals new { Company = aczgc.intCompany, Zone = aczgc.intZone }
                         join ai in db.tbl_CC_AreaInfos on aczgc.intAreaCode equals ai.IDCode
                         where ai.txtPostcodeSubArea == "S63" && dow.txtDOWDescription == "Nday"
                         select t.txtTimeDescription;

The LINQ code returns nothing, it runs through ok (after fixing sulls in the database backend) but just returns nothing and I don't have a clue why?

Matt

 

[Matt_Hirst]

Ok so now figured out it is returning results, can see this by hovering over the DataSource and drilling down through it's attributes, however when I try to bind the results I get the following error:

'cmbTime' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

Searching the internet I see it is something to do with referential integrity of the database. however I can't work out how or why?

Any ideas as to how I can track this down?


Matt

P.S. If I add another dropdownlist and send the results to it there is no problem, if I switch the names around again it works no problem - so I guess this leads me to something to do with the definiton of the control cmbTime - but what?

 

[Matt_Hirst]

sorted it out with a bit more googling and found for some reason I need to include the following:

cmbTime.Items.Clear();
cmbTime.SelectedValue = null;

prior to binding.

At the moment this only seems to affect one of my dropdownlists, which i find very strange.

Matt