Version: 2.5 template: Adapter

I got this error, How could I fix it?

System.Data.SqlClient.SqlException: The multi-part identifier "caromalive2012.dbo.Range.RangeId" could not be bound.

try
Line 1297:          {
Line 1298:              adapter.FetchEntityCollection(collection, filter, numberOfRecordsToReturn, sorter, prefetch);
Line 1299:              return collection;
Line 1300:          }

****

[SqlException (0x80131904): The multi-part identifier "caromalive2012.dbo.Range.RangeId" could not be bound.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
   System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +162
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +115


....

/// <summary>Inits RangeEntity's mappings</summary>
        private void InitRangeEntityMappings()
        {
            base.AddElementMapping( "RangeEntity", "caromalive2012", @"dbo", "Range", 12 );
            base.AddElementFieldMapping( "RangeEntity", "RangeId", "RangeId", false, (int)SqlDbType.Int, 0, 0, 10, true, "SCOPE_IDENTITY()", null, typeof(System.Int32), 0 );
            base.AddElementFieldMapping( "RangeEntity", "RangeName", "RangeName", false, (int)SqlDbType.NVarChar, 250, 0, 0, false, "", null, typeof(System.String), 1 );
            base.AddElementFieldMapping( "RangeEntity", "RangeDescription", "RangeDescription", true, (int)SqlDbType.NVarChar, 500, 0, 0, false, "", null, typeof(System.String), 2 );
            base.AddElementFieldMapping( "RangeEntity", "RangeImageFileName", "RangeImageFileName", true, (int)SqlDbType.NVarChar, 500, 0, 0, false, "", null, typeof(System.String), 3 );
            base.AddElementFieldMapping( "RangeEntity", "CategoryIdFk", "Category_IdFK", true, (int)SqlDbType.Int, 0, 0, 10, false, "", null, typeof(System.Int32), 4 );
            base.AddElementFieldMapping( "RangeEntity", "MetaKeywords", "MetaKeywords", true, (int)SqlDbType.NVarChar, 1000, 0, 0, false, "", null, typeof(System.String), 5 );
            base.AddElementFieldMapping( "RangeEntity", "MetaDescription", "MetaDescription", true, (int)SqlDbType.NVarChar, 1000, 0, 0, false, "", null, typeof(System.String), 6 );
            base.AddElementFieldMapping( "RangeEntity", "PageTitle", "PageTitle", true, (int)SqlDbType.NVarChar, 1000, 0, 0, false, "", null, typeof(System.String), 7 );
            base.AddElementFieldMapping( "RangeEntity", "PageHeading", "PageHeading", true, (int)SqlDbType.NVarChar, 1000, 0, 0, false, "", null, typeof(System.String), 8 );
            base.AddElementFieldMapping( "RangeEntity", "PageDescription", "PageDescription", true, (int)SqlDbType.NVarChar, 1000, 0, 0, false, "", null, typeof(System.String), 9 );
            base.AddElementFieldMapping( "RangeEntity", "Sequence", "Sequence", true, (int)SqlDbType.Int, 0, 0, 10, false, "", null, typeof(System.Int32), 10 );
            base.AddElementFieldMapping( "RangeEntity", "CustomImage", "customImage", true, (int)SqlDbType.VarChar, 255, 0, 0, false, "", null, typeof(System.String), 11 );
        }

The sql is executed bringing data

SELECT DISTINCT [caromalive2012].[dbo].[Range].[RangeId], [caromalive2012].[dbo].[Range].[RangeName], [caromalive2012].[dbo].[Range].[RangeDescription], [caromalive2012].[dbo].[Range].[RangeImageFileName], [caromalive2012].[dbo].[Range].[Category_IdFK] AS [CategoryIdFk], [caromalive2012].[dbo].[Range].[MetaKeywords], [caromalive2012].[dbo].[Range].[MetaDescription], [caromalive2012].[dbo].[Range].[PageTitle], [caromalive2012].[dbo].[Range].[PageHeading], [caromalive2012].[dbo].[Range].[PageDescription], [caromalive2012].[dbo].[Range].[Sequence], [caromalive2012].[dbo].[Range].[customImage] AS [CustomImage] FROM ( [caromalive2012].[dbo].[Range] INNER JOIN [caromalive2012].[dbo].[Product] ON [caromalive2012].[dbo].[Range].[RangeId]=[caromalive2012].[dbo].[Product].[RangeId_FK]) WHERE ( ( [caromalive2012].[dbo].[Product].[CategoryId_FK] = @CategoryIdFk1 AND [caromalive2012].[dbo].[Product].[ProductDewebbed] = @ProductDewebbed2)) ORDER BY [caromalive2012].[dbo].[Range].[Sequence] ASC

What's the query you run? Is the database you want to read from 'caromalive2012' ?

Yes.

even I got this query from llbgen pro.

mjeduar wrote:

Yes.

even I got this query from llbgen pro.

I meant what was the llblgen pro code you ran which produced this SQL

I generated the sql producing a error... (I meant I rename the database from the file PersistenceInfoProvider.cs "base.AddElementMapping( "RangeEntity", "XX_caromalive2012", @"dbo", "Range", 12 );" and this gave me the SQL sentence)... I did this just to get the SQl.... this doesnt have nothing to do with the problem....

Anyway the real problem is "System.Data.SqlClient.SqlException: The multi-part identifier "caromalive2012.dbo.Range.RangeId" could not be bound."(of course this happens when all is well set up)

.... I gonna tell you the history, this is a old project witch is already working but the client didnt have the llbgenpro project so he just gave us the c# code... for that reason I had to recreate everything and for that reason I had to use the version 2.5(while I have everything working),

Now I am trying to generate the same with llblgen(I am close), but now I come across this error.

What I did was replace the entity from the old code (I didnt touch the other code generated by LLBLGEN just the entuty class ) and It doesn't show the error any more(that means the problem comes form the entity)

I have attached the old class entity and the new class entity,

The new one has a new field. anyway I mcompare both files and have some differences.

Could you help me to find out what is the difference that produces the failure?...

Thank you

I'm totally lost on what's the real issue, what is running and what did you change.

So let's take this step at a time.

The error is coming from the database and is complaining about not finding this field "caromalive2012.dbo.Range.RangeId", now the first question, do you have this field inside the mentioned table inside the caromalive2012 database, at the correct server as mentioned in the connection string?

The field caromalive2012.dbo.Range.RangeId yes of course it exists in the database caromalive2012.

What I said is:

if I put the old entity class(I just replace the entity RANGE class in the project generated by LLBLGEN) it works(it has this field "RangeId " mapped)

if I let the project as generated by LLBLGEN(not replacing the range entity) it doesn't work(it has this field "RangeId " mapped) and is when

I get the error "[SqlException (0x80131904): The multi-part identifier "caromalive2012.dbo.Range.RangeId" could not be bound.]"

**For that reason I think that the error is in the entity class range, but not because of the field it exists in both the range entity and in the database. **

It seems very odd.

• First of all, the generate code should work right away, you shouldn't have to modify any of the generated files. If what you said is correct, then you must be changed something else in the generated code, I know you said you didn't but it seems just odd.

• Then, the two entity classes files looks the same despite some little differences on relations you are not using in your query.

• The query you posted, if it is correct it shouldn't rise that exception, unless you are pointing to a database with different column names that the DB you used to create your project. I.e.: Does it run on a DB query window?

• The mappings between fields and DB columns are not stored in the Entity class file, the mappings are stored in the PersistenceInfo files.

My advice is: regenerate the code from you LLBLGen project to a new empty location. Don't modify anything on the generated code. Start with that, run a test and see what happens. Then try to adapt parts of your old code, not the LLBLGen generated code but your custom code (business logic, gui, etc).

I generated the code again, but I continue getting the same error.

So I compared both classes and I found when I change this following line it works.

Just this line was taken from the old code

public static IPrefetchPathElement2 PrefetchPathCategoryCollectionViaProduct
{
get
{
.....
relations.Add(RangeEntity.Relations.ProductEntityUsingRangeIdFk, "__RangeEntity__", "Product_", JoinHint.None); // it works with this line
...
}

Genereted by LLBLGEN

relations.Add(RangeEntity.Relations.ProductEntityUsingRangeIdFk, "RangeEntity__", "Product_", JoinHint.None); //  it doesn't work with this line

So the difference is "RangeEntity__"

I don't understand why it causes the problem.

Could someone explain me what is the possible problem?

It's the alias for the intermediate entity. This was changed years ago, because it could clash with some other elements.

The meaning is that if you filter on the intermediate entity (the one which is aliased) you have to specify that alias as well on the fields/predicates. If you don't, the fields in the predicates refer to the tablenames, not the alias.

Thank you for your suggestion.

**Though, do you have an example?... **

Other question... why when I change the alias name it works?

M.

Please attach the llblgen project file or a repro of it. Also if you can post the complete code snippet where you build the query. i.e. Filter, PrefetchPaths ...etc.

I have attached the llblgen PROJECT

Query.

 public static EntityCollection<RangeEntity> FetchCollectionWithCategoryId(int categoryId, bool onlyOnWeb)
        {
            RelationPredicateBucket filter = new RelationPredicateBucket();

            EntityCollection<RangeEntity> rangeList = new EntityCollection<RangeEntity>( new RangeEntityFactory() );
            filter.PredicateExpression.AddWithAnd( ProductFields.CategoryIdFk == categoryId );
            filter.Relations.Add( ProductEntity.Relations.RangeEntityUsingRangeIdFk );

            if (onlyOnWeb) {filter.PredicateExpression.AddWithAnd(ProductFields.ProductDewebbed == false);}

            PredicateExpression prefetchFilter = new PredicateExpression( CategoryFields.CategoryId == categoryId );

            PrefetchPath2 prefetch = new PrefetchPath2( EntityType.RangeEntity );
            prefetch.Add( RangeEntity.PrefetchPathCategoryCollectionViaProduct, 0, prefetchFilter );

            SortExpression sorter = new SortExpression( RangeFields.Sequence | SortOperator.Ascending );

            return FetchCollection( rangeList, filter, 0, sorter, prefetch );

        }

I can't reproduce it. This is why I've done:

1. Open your project and use the SQL DDL templates to generate a database script that I used to create a DB.

2. Download the lastest v2.5 version, open your project and generate code.

3. Open the generated projects and write a little test app to run your code. This is the query It generates:

SELECT DISTINCT 
    [caroma.devdorf].[dbo].[Range].[RangeId], 
    [caroma.devdorf].[dbo].[Range].[RangeName], 
    [caroma.devdorf].[dbo].[Range].[RangeDescription], 
    [caroma.devdorf].[dbo].[Range].[RangeImageFileName], 
    [caroma.devdorf].[dbo].[Range].[Category_IdFK] AS [CategoryIdFk], 
    [caroma.devdorf].[dbo].[Range].[MetaKeywords], 
    [caroma.devdorf].[dbo].[Range].[MetaDescription], 
    [caroma.devdorf].[dbo].[Range].[PageTitle], 
    [caroma.devdorf].[dbo].[Range].[PageHeading], 
    [caroma.devdorf].[dbo].[Range].[PageDescription], 
    [caroma.devdorf].[dbo].[Range].[Sequence], 
    [caroma.devdorf].[dbo].[Range].[customImage] AS [CustomImage] 

FROM ( [caroma.devdorf].[dbo].[Range]  
    INNER JOIN [caroma.devdorf].[dbo].[Product]  
        ON  [caroma.devdorf].[dbo].[Range].[RangeId]=[caroma.devdorf].[dbo].[Product].[RangeId_FK]) 

WHERE ( ( [caroma.devdorf].[dbo].[Product].[CategoryId_FK] = @CategoryIdFk1 
    AND [caroma.devdorf].[dbo].[Product].[ProductDewebbed] = @ProductDewebbed2)) 
    
ORDER BY [caroma.devdorf].[dbo].[Range].[Sequence] ASC

Parameter: @CategoryIdFk1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.
Parameter: @ProductDewebbed2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

What if I send you my code?

Please check what you do differently compared to what David did above. As your project generates proper code which runs on the DB, there's something else that's wrong.

Your SQL query:

SELECT DISTINCT 
        [caromalive2012].[dbo].[Range].[RangeId], 
        [caromalive2012].[dbo].[Range].[RangeName], 
        [caromalive2012].[dbo].[Range].[RangeDescription], 
        [caromalive2012].[dbo].[Range].[RangeImageFileName], 
        [caromalive2012].[dbo].[Range].[Category_IdFK] AS [CategoryIdFk], 
        [caromalive2012].[dbo].[Range].[MetaKeywords],
        [caromalive2012].[dbo].[Range].[MetaDescription], 
        [caromalive2012].[dbo].[Range].[PageTitle],
        [caromalive2012].[dbo].[Range].[PageHeading], 
        [caromalive2012].[dbo].[Range].[PageDescription],
        [caromalive2012].[dbo].[Range].[Sequence], 
        [caromalive2012].[dbo].[Range].[customImage] AS [CustomImage] 
FROM ( [caromalive2012].[dbo].[Range] 
        INNER JOIN [caromalive2012].[dbo].[Product] ON [caromalive2012].[dbo].[Range].[RangeId]=[caromalive2012].[dbo].[Product].[RangeId_FK]) 
WHERE ( ( [caromalive2012].[dbo].[Product].[CategoryId_FK] = @CategoryIdFk1 
    AND [caromalive2012].[dbo].[Product].[ProductDewebbed] = @ProductDewebbed2)) 
ORDER BY [caromalive2012].[dbo].[Range].[Sequence] ASC

No aliases used, none required.

As the error is: "caromalive2012.dbo.Range.RangeId" could not be found, and as caromalive2012.dbo.Range is in the FROM clause (see SQL above), the only reasons can be:

• caromalive2012 is not the catalog you're connected to. (Check this)
• dbo is not the schema Range is in (Check this)
• Range doesn't have a field RangeId (Check this)

I'd like to ask you to check all of these above. The first item is that you check the connectionstring of your application whether you connect to the right server and catalog.

I checked everything....
And as I said before if I change the line mentioned above it works...

There is something missing here, or maybe miscommunication.

Let's take a step back.

Now without fixing the generated code, please leave it as it is. Could you please post the generated SQL code, and try to run it (copy & paste to SQL Mgt Studio) against the database, and report the outcome.

SELECT DISTINCT [caromalive2012].[dbo].[Range].[RangeId], [caromalive2012].[dbo].[Range].[RangeName], [caromalive2012].[dbo].[Range].[RangeDescription], [caromalive2012].[dbo].[Range].[RangeImageFileName], [caromalive2012].[dbo].[Range].[Category_IdFK] AS [CategoryIdFk], [caromalive2012].[dbo].[Range].[MetaKeywords], [caromalive2012].[dbo].[Range].[MetaDescription], [caromalive2012].[dbo].[Range].[PageTitle], [caromalive2012].[dbo].[Range].[PageHeading], [caromalive2012].[dbo].[Range].[PageDescription], [caromalive2012].[dbo].[Range].[Sequence], [caromalive2012].[dbo].[Range].[customImage] AS [CustomImage] FROM ( [caromalive2012].[dbo].[Range] INNER JOIN [caromalive2012].[dbo].[Product] ON [caromalive2012].[dbo].[Range].[RangeId]=[caromalive2012].[dbo].[Product].[RangeId_FK]) WHERE ( ( [caromalive2012].[dbo].[Product].[CategoryId_FK] = '1481' AND [caromalive2012].[dbo].[Product].[ProductDewebbed] = '0')) ORDER BY [caromalive2012].[dbo].[Range].[Sequence] ASC

I ran it, it worked

result 31 row(s) returned (203 ms)

!is the weirdest thing I've ever seen! Any suggestion?

IMHO, it should be something simple to fix but not obvious. Personally I think you are connecting to another DB, or at least to some DB that has been changed and it's not the same DB used to create the LLBLGen Project.

Please double check the things pointed by Frans:

Otis wrote:

• caromalive2012 is not the catalog you're connected to. (Check this)
• dbo is not the schema Range is in (Check this)
• Range doesn't have a field RangeId (Check this)

I'd like to ask you to check all of these above. The first item is that you check the connectionstring of your application whether you connect to the right server and catalog.

I know you said you did, but please double check them. Remember that the connectionstring you have to check is in your app project (Console, Test, WinForm, etc) not at the DBSpecific project.

If you face the same exception, please prepare a tiny repro solution for us, so we can reproduce your situation. The solution should contain: LLBLGen Project (we already have that), DDL schema script (without data), VSNet project (no dlls, no /bin /obj, just code files), all zipped, please include only relevant things.

I have attached a small project not dll included,

it shows the error once you run it

I ran it, it worked

result 31 row(s) returned (203 ms)

!is the weirdest thing I've ever seen! Any suggestion?

If the SQL works on the database, then most probably the application has a connection string that points to the wrong server/database. PLease check this out, and check whether you are using any catalogNameOverwrites or schemaNameOverwrites.

As I said before, I already checked this before. It works if I change the alias name in the range entity( as described above), if I would have pointed it to other database it had not worked when I change the code...

Anyway for that reason I attached the code -> it shows the error.

Any suggestion?

Your code shows a connection string which points to the catalog caromalive2012, on server twmg-bed9fdff03\SQLEXPRESS.

The query contains references to caromalive2012 as catalog, so this all should work.

The thing is however, that your llblgen pro project doesn't have caromalive2012 as catalog, but caroma.devdorf. The generated code, generated with this project therefore also has caroma.devdorf in itself. The odd thing is is that when I try to refresh the catalog, the server it tries to connect to is twmg-bed9fdff03\SQLEXPRESS.

So there are apparently two catalogs on this server: caromalive2012 and caroma.devdorf.

The generated code you contained in the project contains mappings to caromalive2012. This doesn't match the .lgp project you attached earlier, which refers to caroma.devdorf.

So, my guess is that your application should connect to caroma.devdorf, but instead connects to caromalive2012, and you use generated code targeting that catalog. (which is apparently created with another .lgp file, not the one you attached above, correct?)

-devdorf is the same database(the estruture are exactly the same) then find and replace in the llblgen code

What I did.

Anyway I deleted caromalive2012 and just leaving the devdorf(I deleted all the databases of course I changed my connection string in the code as well) it happens exactly the same but it works when I change the line of code mentined above(when I say it works is because it pull out the data from the database and is showed in my web app controls)

so it can't connect to any other database

In this moment I don't know what to do because the only hope was that the application was connecting to different database. I deleted all my databases so ?

Any suggestion?