none
The multi-part identifier "alias.field" could not be bound???

    Question

  • Hi,

    When I try execute one query in SQL 2k5, with alias in order by clausule, I retrieve the follow message:

    Server: Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "alias.fields" could not be bound.

    Where alias is a any alias and, fields is a field of the table with alias.

    Already exists one fix to patch this?

    Thanks

    Friday, April 28, 2006 6:33 PM

Answers

  • In your select statement there is no reference to this table (rentalCostTable), so that's why you're getting the error:

    SELECT locationTypesTable.locationTypes, rentalCostTable.rentalCost, PFR.shortDescription, PFR.propertyRef, propertyTypesTable.propertyType, PFR.numBeds
     FROM propertysForRentTable PFR
     INNER JOIN
      propertyTypesTable ON PFR.typeOfProperty = propertyTypesTable.keyID
     INNER JOIN
      locationTypesTable ON PFR.location = locationTypesTable.keyID 

    In order to resolve the error, you must include this table in the FROM clause, e.g. JOIN with it based on some field.

    SELECT locationTypesTable.locationTypes, rentalCostTable.rentalCost, PFR.shortDescription, PFR.propertyRef, propertyTypesTable.propertyType, PFR.numBeds
     FROM propertysForRentTable PFR
     INNER JOIN
      propertyTypesTable ON PFR.typeOfProperty = propertyTypesTable.keyID
     INNER JOIN
      locationTypesTable ON PFR.location = locationTypesTable.keyID 
    
    INNER JOIN rentalCostTable ON
    PFR.RentalID = rentalCostTable.RentalID -- use the actual field to join the tables


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, June 17, 2010 11:06 PM
  • This was a bug in the older versions of SQL Server. We fixed the problems with the ORDER BY clause in SQL Server 2005. This is documented under the following topic:
     
     
    You should also check out the other backward compatibility topics:
     
     
     
    Also, I should note that the query above will run fine if the database compatibility level is set to 80 or below. You can do this by using the sp_dbcmptlevel system SP but please read the corresponding topic in Books Online before going this route. The database compatibility level for databases upgraded from SQL Server will automatically be at 80 so your existing code should run fine.
    Friday, May 05, 2006 12:16 AM
  • Greetings.  I ran into this because I had alias conflicts where the table name and the field name were the same.  Once I gave the table a true alias (e.g. "z3") with no conflict, it worked fine.

     UPDATE #LocTracker

    SET ErrorCode = 3 --'Unknown ZIP3'

    FROM #LocTracker lt

    LEFT OUTER JOIN US_TC_RMS_DB_MGMT..ZIP3 ON zip3.ZIP3 = LEFT(lt.POSTALCODE,3)

    WHERE lt.POSTALCODE is null

    AND ErrorCode = 0

     Gave me this error:

    The multi-part identifier "zip3.ZIP3" could not be bound.

      

    Certainly zip3.ZIP3 referencing is not Best Practices.

    UPDATE can be very tricky. Note above that #LocTracker is used after the UPDATE then after the FROM with alias. While valid T-SQL, I always found it confusing.

    Referring to the sample below, I just put the table name with alias after FROM, and after UPDATE I use the table alias. I also use table alias for each column.

    Same consideration if you are JOINing to subselect (derived table).

    The advantage of such an approach is that you take out the guesswork from the query. You tell precisely the database engine what should it do.


    USE tempdb; SELECT * INTO SOH FROM AdventureWorks2008.Sales.SalesOrderHeader SELECT * INTO SOD FROM AdventureWorks2008.Sales.SalesOrderDetail -- Bad reference I meant to type b.ProductID UPDATE a SET a.TotalDue = a.TotalDue * 1.05 FROM SOH a INNER JOIN SOD b ON a.SalesOrderID = b.SalesOrderID WHERE c.ProductID != 800 GO /* Msg 4104, Level 16, State 1, Line 12 The multi-part identifier "c.ProductID" could not be bound. */ -- Correct reference, successful query UPDATE a SET a.TotalDue = a.TotalDue * 1.05 FROM SOH a INNER JOIN SOD b ON a.SalesOrderID = b.SalesOrderID WHERE b.ProductID != 800 GO -- (31366 row(s) affected) DROP TABLE SOH DROP TABLE SOD


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, November 21, 2009 7:36 AM
  • Mine was a total bonehead mistake.  I mis-spelled the table name in the SELECT field.


    IntelliSense may be helpful (starting with SQL Server 2008):


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Wednesday, September 15, 2010 11:34 PM
  •  Anyone here have any ideas?


    It might have been case-sensitive collation in the database where you got the error. In a case-sensitive collation, the case must be precise.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, December 14, 2010 7:00 PM

All replies

  • Send the SQL that is causing this.
    Friday, April 28, 2006 7:18 PM
  • Hi,

    sorry for my english, I am Brazilian. Ok, follow the sql:

    begin transaction


    create table #tblAlias (a int, b char(10));
    create table #tblAliasB (a int, b char(10));

    --INSERT VALUES INTO TABLE #tblAlias
    insert into #tblAlias values (1, 'USUARIO A');
    insert into #tblAlias values (2, 'USUARIO B');
    insert into #tblAlias values (3, 'USUARIO C');
    insert into #tblAlias values (4, 'USUARIO D');
    insert into #tblAlias values (5, 'USUARIO E');
    insert into #tblAlias values (6, 'USUARIO F');
    insert into #tblAlias values (7, 'USUARIO G');

    --INSERT VALUES INTO TABLE #tblAliasB
    insert into #tblAliasB values (1, 'USUARIO A');
    insert into #tblAliasB values (2, 'USUARIO B');
    insert into #tblAliasB values (3, 'USUARIO C');
    insert into #tblAliasB values (4, 'USUARIO D');
    insert into #tblAliasB values (5, 'USUARIO E');
    insert into #tblAliasB values (6, 'USUARIO F');
    insert into #tblAliasB values (7, 'USUARIO G');


    SELECT A, B
     FROM #tblAlias
     WHERE B LIKE 'USUARIO%'
     ORDER BY A DESC;

    SELECT TBLALIAS.A fieldA, TBLALIAS.B fieldB
     FROM #tblAlias TBLALIAS
     WHERE TBLALIAS.B LIKE 'USUARIO%'
      ORDER BY anyaliasnotdeclared.fieldA DESC; /*I think that it's impossible, but, in my company this happened.

    in the sql2k this is avaliable*/

    rollback transaction

     

    Friday, April 28, 2006 7:58 PM
  • I think you are correct, you can not use a not used table or table alias in the order by clause.
    Saturday, April 29, 2006 9:54 PM
  • This was a bug in the older versions of SQL Server. We fixed the problems with the ORDER BY clause in SQL Server 2005. This is documented under the following topic:
     
     
    You should also check out the other backward compatibility topics:
     
     
     
    Also, I should note that the query above will run fine if the database compatibility level is set to 80 or below. You can do this by using the sp_dbcmptlevel system SP but please read the corresponding topic in Books Online before going this route. The database compatibility level for databases upgraded from SQL Server will automatically be at 80 so your existing code should run fine.
    Friday, May 05, 2006 12:16 AM
  • when i run this statement after where statement, am getting this error.

     

    Run-time error '4104' The multi-part identifier "gdb_school.school_num could not be bound"

     

     

    WHERE ((gdb_school.school_num) > 0) AND (([gdb_school].region_num)=4) AND (([gdb_school].district_num)=402) AND (([gdb_school].[resp_agency])=2)

     

    can someone help me ?

    Monday, March 17, 2008 7:25 AM
  •  

    Hi,

     

    Use alias name to the tables insted of giving tablename.columnname.

     

    select * from gdb_school gdb

    WHERE ((gdb.school_num) > 0) AND ((gdb.region_num)=4) AND ((gdb.district_num)=402) AND ((gdb.[resp_agency])=2)

     

    Hope it helps you.

     

    Thanks,

    Radhika.

    Friday, May 09, 2008 5:10 AM
  • I resolved this issue by adding the Table name in the  FROM table list...


    Tuesday, July 22, 2008 10:53 AM
  • I came across this thread because I am using an adp with SQL Server 2005 backend. I was getting all sorts of strange write conflict errors on a sub form, when updating a record, moving record then back again and making more changes. After this I was getting the message "Multi part identifier A.AuditDate could not be bound".

     

    Anyway, after a lot of messing around and research, it turns out that I had the recordsource of the subform as something along the lines of Select ... from tblAudit A order by A.AuditDate. Once I removed the alias in the order by clause, everything was fine (ie Select ... from tblAudit order by AuditDate)

     

    Just thought I'd pass this on in case anyone else runs into this problem.

    Friday, July 25, 2008 11:19 PM
  • hi all,

    i have just came to search the same topic, i have found good ideas over here, however i am using below query to insert from one database to another. keeping the idea in mind that both databases might be on different machines/servers

    Insert Into [xion\sqlexpress].[APS_EmpAttendance]
    ( [xion\sqlexpress].[APS_EmpAttendance].[dbo].EmployeeNo,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].AttDate,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].DateIn,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].TimeIn,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].DateOut, 
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].TimeOut,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].Shift,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].ShiftHrs,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].GenOTime,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].OffOTime,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].EarOTime,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].ExtOtime, 
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].GazOtime,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].LateHrs,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].EarlyHrs,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].WorkHrs,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].PaidHrs,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].UnPaidHrs,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].OnLeave, 
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].LeaveCode,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].Absent,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].OffDay,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].GazDay,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].PhyPresent,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].OffPresent,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].GazPresent,
    [xion\sqlexpress].[APS_EmpAttendance].[dbo].IsProcessed ) 
    select  [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].EmployeeNo,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].RDate,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].DateIn, 
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].TimeIn,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].DateOut,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].TimeOut, 
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].Shift,
    [xion\sqlexpress].[tis-millac].[dbo].hrtShiftPolicy.TotHours, 
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].GenOTime,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].OffOTime,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].EarOTime,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].ExtOtime, 
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].GazOtime,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].LateHour,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].EarlyHour, 
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].WorkHour,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].PaidHour,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].UnPaidHour,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].OnLeave, 
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].Leaveid,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].Absent,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].OffDay, 
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].GazDay,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].Norpreset,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].OffPresent,
    [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].GazPresent,  0 as IsProcessed 
    FROM [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance]
    INNER JOIN [xion\sqlexpress].[tis-millac].[dbo].HRTShiftPolicy 
    ON [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].Shift = [xion\sqlexpress].[tis-millac].[dbo].HRTShiftPolicy.ShiftCode 
    INNER JOIN [xion\sqlexpress].[tis-millac].[dbo].HRTEmployee
    ON [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].EmployeeNo = [xion\sqlexpress].[tis-millac].[dbo].HRTEmployee.EmployeeNo 
    Where [xion\sqlexpress].[tis-millac].[dbo].HRTEmployee.Active = 1 
    And [xion\sqlexpress].[tis-millac].[dbo].[HRTAttendance].RDate Between '01-May-2008' And '31-May-2008'

    i recevied message regarding all fields. that

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "xion\sqlexpress.tis-millac.dbo.<Field>" could not be bound.

    however when i removed server alias [xion\sqlexpress] the query executed successfully. can any one assist me in ?
    Sunday, July 27, 2008 2:02 AM
  • Greetings.  I ran into this because I had alias conflicts where the table name and the field name were the same.  Once I gave the table a true alias (e.g. "z3") with no conflict, it worked fine.

     

    UPDATE #LocTracker

    SET ErrorCode = 3 --'Unknown ZIP3'

    FROM #LocTracker lt

    LEFT OUTER JOIN US_TC_RMS_DB_MGMT..ZIP3 ON zip3.ZIP3 = LEFT(lt.POSTALCODE,3)

    WHERE lt.POSTALCODE is null

    AND ErrorCode = 0

     

    Gave me this error:

    The multi-part identifier "zip3.ZIP3" could not be bound.

     

     

    Monday, September 15, 2008 3:41 PM
  • I thought I'd post this since it was driving me insane

    update

     

    [Legacy-Inventory-Map]
    set a.[ProdCode] = b.[ProdCode]
    from
    [Legacy-Inventory-Map] AS a inner join [New Inventory] AS b on a.[New Inventory ID] = B.id

    failed because the multi-part identifer a.[ProdCode] could not be bound, the solution?

    update a
    set a.[ProdCode] = b.[ProdCode]
    from
    [Legacy-Inventory-Map] AS a inner join [New Inventory] AS b on a.[New Inventory ID] = B.id

    the field source for the active part of the command comes from the first argument of the statement, not the recordsource argument

    • Proposed as answer by Roger Moore Tuesday, March 09, 2010 8:47 PM
    Wednesday, April 08, 2009 4:26 PM
  • You just need to check any typing mistake in your where clause.

    Just Recheck all and enjoy error free Query.

    ChEtAn PaTeL

    Visit My Site
    Saturday, April 18, 2009 9:11 AM
  • Just another thing to look for/watch...I found that the reported error for the line where the "field" could not be bound ended up being off...spent 30 min. double checking query section as a stand-alone (query portion was part of stored procedure that ran a few queries) and could not find error until I started piecing entire Query together part-by-part until error occurred and then just analyzed the new section that caused error and of course, it was plain as day...but not at the originally reported line...
    HTH
    Dave
    Monday, September 14, 2009 3:35 PM
  • UPDATE #LocTracker
    SET ErrorCode = 3 --'Unknown ZIP3'
    FROM #LocTracker lt
    LEFT OUTER JOIN US_TC_RMS_DB_MGMT ZIP3 ON zip3.ZIP3 = LEFT(lt.POSTALCODE,3)
    WHERE lt.POSTALCODE is null
    AND ErrorCode = 0

    Not 100% sure but don't need ".."

    Dave
    Monday, September 14, 2009 3:39 PM
  • It means that "alias.fields" is not an int or double etc. but more like a array/table.

    For example the following SQL generates the error: The multi-part identifier "Table1.ID" could not be bound.
    UPDATE Table1
    SET FieldName=Temp.FieldName
    FROM (SELECT FieldName FROM Table2 WHERE Table2.ID=Table1.ID) Temp
    To solve the problem you could do like:

    UPDATE Table1
    SET FieldName=Temp.FieldName
    FROM (SELECT FieldName, ID FROM Table2) Temp
    WHERE Temp.ID=Table1.ID

    Thursday, November 05, 2009 9:30 AM
  • I did the same and it resolved, thanks Brother
    maqk loves to code
    Tuesday, November 10, 2009 10:11 AM
  • Greetings.  I ran into this because I had alias conflicts where the table name and the field name were the same.  Once I gave the table a true alias (e.g. "z3") with no conflict, it worked fine.

     UPDATE #LocTracker

    SET ErrorCode = 3 --'Unknown ZIP3'

    FROM #LocTracker lt

    LEFT OUTER JOIN US_TC_RMS_DB_MGMT..ZIP3 ON zip3.ZIP3 = LEFT(lt.POSTALCODE,3)

    WHERE lt.POSTALCODE is null

    AND ErrorCode = 0

     Gave me this error:

    The multi-part identifier "zip3.ZIP3" could not be bound.

      

    Certainly zip3.ZIP3 referencing is not Best Practices.

    UPDATE can be very tricky. Note above that #LocTracker is used after the UPDATE then after the FROM with alias. While valid T-SQL, I always found it confusing.

    Referring to the sample below, I just put the table name with alias after FROM, and after UPDATE I use the table alias. I also use table alias for each column.

    Same consideration if you are JOINing to subselect (derived table).

    The advantage of such an approach is that you take out the guesswork from the query. You tell precisely the database engine what should it do.


    USE tempdb; SELECT * INTO SOH FROM AdventureWorks2008.Sales.SalesOrderHeader SELECT * INTO SOD FROM AdventureWorks2008.Sales.SalesOrderDetail -- Bad reference I meant to type b.ProductID UPDATE a SET a.TotalDue = a.TotalDue * 1.05 FROM SOH a INNER JOIN SOD b ON a.SalesOrderID = b.SalesOrderID WHERE c.ProductID != 800 GO /* Msg 4104, Level 16, State 1, Line 12 The multi-part identifier "c.ProductID" could not be bound. */ -- Correct reference, successful query UPDATE a SET a.TotalDue = a.TotalDue * 1.05 FROM SOH a INNER JOIN SOD b ON a.SalesOrderID = b.SalesOrderID WHERE b.ProductID != 800 GO -- (31366 row(s) affected) DROP TABLE SOH DROP TABLE SOD


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, November 21, 2009 7:36 AM
  • Thanks, Radhika.

    Aliasing the tables did the trick for me!

    --Garrett
    Tuesday, January 05, 2010 10:08 PM
  • Thanks Chris M for the tip!  I was trying to get the following query to work, but I kept getting the "bound" error because it didn't have the FROM clause:

    UPDATE dbo.tblBenchmarkData SET dbo.tblBenchmarkData.IDZEGCode = dbo.tblZEGCode.IDZEGCode
    WHERE dbo.tblZEGCode.ZEGCodeID = tblBenchmarkData.ZEGCodeID
    	AND dbo.tblZEGCode.IDZEGCode <> dbo.tblBenchmarkData.IDZEGCode
    Here is the corrected query:

    UPDATE a SET a.IDZEGCode = b.IDZEGCode
    FROM dbo.tblBenchmarkData AS a INNER JOIN dbo.tblZEGCode AS b ON a.ZEGCodeID = b.ZEGCodeID
    WHERE a.IDZEGCode <> b.IDZEGCode
    Tuesday, March 09, 2010 8:50 PM
  • I ran into same error last night & after putting in print statements at various places, I found out that it was SQL Server who failed to detect that I used invalid aliases in WHERE clause.

    Also during this debugging session, I noticed that line numbers reported in error message were way off.

    I'm not sure whether these are bugs or features :)

    Friday, April 23, 2010 7:23 AM
  • Hi Kalman,

     

    I am also getting bound errors in below query plz suggest

     

    select

     

    account

    .account as account,

    account_udt

    .grade as grade,

    userinfo

    .username as account_manager,

    account

    .type as type,

    isnull

     

    (contact.prefix,'') + ' ' + isnull(contact.firstname,'') + ' ' +

    isnull

     

    (contact.middlename,'') + ' ' + isnull(contact.lastname,'') as contact_name,

    isnull

     

    (contact.title,'') as title

    ,

     

    isnull(contact_udt.functions,'') as "function"

    from

     

    sysdba.account inner join sysdba.account_udt on

    account

    .accountid = account_udt.accountid

    inner

     

    join sysdba.userinfo on account.accountmanagerid = userinfo.userid

    left

     

    outer join sysdba.contact on account.accountid = contact.accountid

    left

     

    outer join sysdba.contact_udt on contact.contactid = contact_udt.contactid

    where

     

    account_udt.status = 'Active' and (contact.status = 'active' or contact.status is null)

    -

     

    order by account

    --select * from sysdba.account


    Rahul Soni SQL DBA
    Sunday, April 25, 2010 5:53 PM
  • Can someone help?

    I did this:

    UPDATE simplefe_xref_location--, 	simplefe_xref_location_tsl_ownership 
    	SET simplefe_xref_location.flag 
    	= [simplefe_xref_location_tsl_ownership].[LOCATION_FLAG];

    and got this error:

    Msg 4104, Level 16, State 1, Line 3
    The multi-part identifier "simplefe_xref_location_tsl_ownership.LOCATION_FLAG" could not be bound.
    

    I checked table and column "simplefe_xref_location_tsl_ownership.LOCATION_FLAG" does exist.

    Friday, May 28, 2010 2:34 PM
  • Hi all, thought I'd post here as it seems the most knowledge people are here.

    I've spent last few days Googling this and reading that and nothings worked for me yet!! Any Ideas for an answer for this problem would be a big help.

    Similar problem to above (although none of above has made my code work!) I have an SQL database with various tables all in the same place (use same connection string in an SQL database). But if I run the below code to populate a Gridview it works fine if I DONT include the rentalCostTable.rentalCost section, but when I do I get the following error.

    The multi-part identifier "rentalCostTable.rentalCost" could not be bound.

    <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:dbLLConnectionString %>" 
     SelectCommand="SELECT locationTypesTable.locationTypes, rentalCostTable.rentalCost, PFR.shortDescription, PFR.propertyRef, propertyTypesTable.propertyType, PFR.numBeds
     FROM propertysForRentTable PFR
     INNER JOIN
       propertyTypesTable ON PFR.typeOfProperty = propertyTypesTable.keyID
     INNER JOIN
       locationTypesTable ON PFR.location = locationTypesTable.keyID ">
     
     <SelectParameters>
     <asp:ControlParameter ControlID="locationDDList" Name="locID" PropertyName="SelectedValue" Type="String" />
     <asp:ControlParameter ControlID="propTypeDDList" Name="propType" PropertyName="SelectedValue" Type="String" />
     <asp:ControlParameter ControlID="minCostDDList" Name="minCost" PropertyName="SelectedValue" Type="String" />
     <asp:ControlParameter ControlID="maxCostDDList" Name="maxCost" PropertyName="SelectedValue" Type="String" />
     <asp:ControlParameter ControlID="numBedsDDList" Name="bedsID" PropertyName="SelectedValue" Type="String" />
     </SelectParameters>
     </asp:SqlDataSource>

    As you can see Ive tried aliases, I also tried putting it after the FROM statement too, but that just gave errors about not finding propertysForRentTable!! It should work fine but doesn't so any ideas?

    If you need extra info please ask, Many Thanks in advance.

    Cheers

    Trevor

     p.s. Im using VWD 2010 express edition and SQL Managament Studio 2010 on Windows  7 Ultimate.

     

     

    • Edited by tizard Thursday, June 17, 2010 9:48 PM make code readable size
    Thursday, June 17, 2010 9:46 PM
  • In your select statement there is no reference to this table (rentalCostTable), so that's why you're getting the error:

    SELECT locationTypesTable.locationTypes, rentalCostTable.rentalCost, PFR.shortDescription, PFR.propertyRef, propertyTypesTable.propertyType, PFR.numBeds
     FROM propertysForRentTable PFR
     INNER JOIN
      propertyTypesTable ON PFR.typeOfProperty = propertyTypesTable.keyID
     INNER JOIN
      locationTypesTable ON PFR.location = locationTypesTable.keyID 

    In order to resolve the error, you must include this table in the FROM clause, e.g. JOIN with it based on some field.

    SELECT locationTypesTable.locationTypes, rentalCostTable.rentalCost, PFR.shortDescription, PFR.propertyRef, propertyTypesTable.propertyType, PFR.numBeds
     FROM propertysForRentTable PFR
     INNER JOIN
      propertyTypesTable ON PFR.typeOfProperty = propertyTypesTable.keyID
     INNER JOIN
      locationTypesTable ON PFR.location = locationTypesTable.keyID 
    
    INNER JOIN rentalCostTable ON
    PFR.RentalID = rentalCostTable.RentalID -- use the actual field to join the tables


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, June 17, 2010 11:06 PM
  • Hi, thanks for such a prompt reply, shame I didn't see it sooner, was having a bit of trouble it seems as could not find this posting in my profile under forums written in (?) plus couldn't find it by searching AND I only got a alert from windows live to say I had a reply the other day (26th). So thank you.

    To be honest I sorted a day or two after on my own but I didn't do it this way (although I had tried this already after a friend reviewed my code and that wouldn't work for me either) and I'll post up what I did to get it to work when my pc has finished reinstalling Windows.

    Cheers

    Trevor

    Wednesday, June 30, 2010 5:03 PM
  • Mine was a total bonehead mistake.  I mis-spelled the table name in the SELECT field.
    Wednesday, September 15, 2010 10:08 PM
  • Mine was a total bonehead mistake.  I mis-spelled the table name in the SELECT field.


    IntelliSense may be helpful (starting with SQL Server 2008):


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Wednesday, September 15, 2010 11:34 PM
  • he is right. 

     

    you can also simple do this and it fixes the problem:  (just in case you need to keep the same name)

     

     

    select * from gdb_school gdb_school

    WHERE ((gdb_school.school_num) > 0) AND ((gdb_school.region_num)=4) AND ((gdb_school.district_num)=402) AND ((gdb_school.[resp_agency])=2)

     

    Friday, November 19, 2010 1:29 AM
  • Ah, I was wondering about the fact that some where using the [] delimiters and one wasn't.

    Okay, maybe someone can answer this slight variant for me. I know I've seen this same error occur in the past, when writing a query where I'd specify the table name in the FROM clause using the fully-qualified name (MyDb.dbo.Employee) but when I'd specify a column in a restriction, I'd only use the table name itself (Employee). An example:

    SELECT *

    FROM    BaseDb.dbo.Employee

    WHERE  Employee.EmployeeId = 5

     

    I'm seeing this run on some servers without an error, though I could've sworn I'd seen it happen against one of those servers before. However, on some servers it gets the error. I've played with different versions of SqlServer (2005, 2008), tried different compatibility levels going back as far as 70 (SqlServer 7.0) and can't make it happen on my internal test systems. However, I'm almost certain I've gotten the error before, and it's showing up on a client db now. I'm suspecting maybe some configuration setting, but I have no idea what it might be. So far I've not found any clues through my online searches. Anyone here have any ideas?

     

    Tuesday, December 14, 2010 6:36 PM
  •  Anyone here have any ideas?


    It might have been case-sensitive collation in the database where you got the error. In a case-sensitive collation, the case must be precise.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, December 14, 2010 7:00 PM
  • Thanks, this helped fix a problem I was having, by seeing your posting.

    Best!

    Tuesday, March 29, 2011 9:06 PM
  • Thanks SQLUSA you´re the best!!!!
    Wednesday, October 19, 2011 11:08 AM
  • Thanks Zubairqau. I followed your tip and resolved this issue by adding the Table name in the  FROM table list...
    Thursday, September 20, 2012 1:36 PM
  • Hi,

    Use alias name to the tables insted of giving tablename.columnname.

    select * from gdb_school gdb

    WHERE ((gdb.school_num) > 0) AND ((gdb.region_num)=4) AND ((gdb.district_num)=402) AND ((gdb.[resp_agency])=2)

    Hope it helps you.

    Thanks,

    Radhika.

    Really useful! Thanks
    Thursday, January 31, 2013 8:58 AM