Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1477
  • Last Modified:

The multi-part identifier "X" could not be bound

I created a crystal report based on a view, the view code is as following;

USE [Live_45]
GO

/****** Object:  View [dbo].[cst_adStuStatusChangesUpdate_jy_vw]    Script Date: 09/04/2012 17:11:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[cst_adStuStatusChangesUpdate_jy_vw]
AS
SELECT     rpt_AdStatusChanges_vw.BeginDate, rpt_AdStatusChanges_vw.DateAdded, rpt_AdStatusChanges_vw.Status,
                      rpt_AdStatusChanges_vw.SySchoolStatusDescrip, rpt_AdStatusChanges_vw.StatusSort, rpt_AdStatusChanges_vw.PrevSySchoolStatusID,
                      rpt_AdStatusChanges_vw.PrevStatus, rpt_AdStatusChanges_vw.NewSySchoolStatusID, rpt_AdStatusChanges_vw.NewStatDescrip,
                      rpt_AdStatusChanges_vw.SySchoolStatusID, rpt_AdStatusChanges_vw.CurrentStatus, rpt_AdStatusChanges_vw.EffectiveDate,
                      CAST(rpt_AdStatusChanges_vw.InternalComm AS varchar(899)) AS InternalComm, rpt_AdStatusChanges_vw.ByUser, rpt_AdStatusChanges_vw.Counter,
                      rpt_AdStatusChanges_vw.Category, rpt_AdStatusChanges_vw.CategoryStatus, rpt_AdStatusChanges_vw.Enrollments,
                      rpt_AdStatusChanges_vw.StudentName, rpt_AdStatusChanges_vw.SSN, rpt_AdStatusChanges_vw.enrollNum, rpt_AdStatusChanges_vw.ApplDate,
                      rpt_AdStatusChanges_vw.StuNum, rpt_AdStatusChanges_vw.SyStudentId, '            ' AS PrintID, rpt_AdStatusChanges_vw.EnrollID,
                      rpt_AdStatusChanges_vw.StudentID, rpt_AdStatusChanges_vw.Comment, rpt_AdStatusChanges_vw.PgmVerCde, rpt_AdStatusChanges_vw.ShiftDesc,
                      rpt_AdStatusChanges_vw.Campus, rpt_AdStatusChanges_vw.DateStarted, rpt_AdStatusChanges_vw.EnrollDescrip,
                      rpt_AdStatusChanges_vw.fBeginDate, rpt_AdStatusChanges_vw.fReturnDate, rpt_AdStatusChanges_vw.SycampusID,
                      rpt_AdStatusChanges_vw.ProgramVersion, rpt_AdStatusChanges_vw.CampusCode, rpt_AdStatusChanges_vw.StudentNameEnroll,
                      rpt_AdStatusChanges_vw.Reason, rpt_AdStatusChanges_vw.NewStatCategory, rpt_AdStatusChanges_vw.StoredLDA,
                      rpt_AdStatusChanges_vw.StudentPhone, rpt_AdStatusChanges_vw.StudentAddress, rpt_AdStatusChanges_vw.StudentCity,
                      rpt_AdStatusChanges_vw.StudentState, rpt_AdStatusChanges_vw.StudentCountry, rpt_AdStatusChanges_vw.StudentZip,
                      rpt_AdStatusChanges_vw.NSLDSWithdrawalDate, rpt_AdStatusChanges_vw.DropDate, dbo.SyStaff.SyStaffID, dbo.SyStaff.FirstName,
                      dbo.SyStaff.LastName
FROM         dbo.rpt_AdStatusChanges_vw INNER JOIN
                      dbo.syStudent WITH (nolock) ON rpt_AdStatusChanges_vw.SyStudentID = SyStudent.SyStudentID INNER JOIN
                      dbo.SyStaff ON dbo.syStudent.AmRepID = dbo.SyStaff.SyStaffID LEFT OUTER JOIN
                      dbo.AdEnroll WITH (nolock) ON adenroll.adenrollid = rpt_AdStatusChanges_vw.enrollid
WHERE     (1 = 1)

GO


When I tried to run the report, I got the following error:

The multi-part identifier "cst_adStuStatusChangesUpdate_jy_vw.SyCampusID" could not be bound. (Source: Microsoft OLE DB Provider for SQL Server).

Any help will be great appreciated, thank you.
CV-error.jpg
0
Jason Yu
Asked:
Jason Yu
  • 11
  • 7
1 Solution
 
momi_sabagCommented:
what happens when you run the following query

select SyCampusID from cst_adStuStatusChangesUpdate_jy_vw
0
 
mlmccCommented:
That would tend to indicate that particular column doesn't exist.  Verify the spelling.
Capitalization may make a difference

mlmcc
0
 
mlmccCommented:
Does this run in SQL Manager?

I found at times with Crystal that a view got too long to be used.  You might try aliasing the view and table names so you don't have to repeat them in the SELECT as shown below

SELECT AD.SyCampusID
FROM dbo.rpt_AdStatusChanges_vw  AD

mlmcc
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Jason YuAuthor Commented:
I run the command you listed, and it give me syCampusID =11 for each row.

I checked the code, there is a line like this:

 rpt_AdStatusChanges_vw.fBeginDate, rpt_AdStatusChanges_vw.fReturnDate, rpt_AdStatusChanges_vw.SycampusID,

I was wondering why it doesn't use cst_adStuStatusChangesUpdate_jy_vw as a prefix instead of using another view's name as the prefix, does this matter. Do I need change all the prefix in this view to cst_adStuStatusChangesUpdate_jy_vw which is the new view.

Forgive me bad understanding, I am quite new to TSQL and View.

thank you.
0
 
mlmccCommented:
No.  You prefix the cloolumn names witht he table or view name they come from.

Note your view uses rpt_AdStatusChanges_vw.SycampusID but the select statement suggested uses rpt_AdStatusChanges_vw.syCampusID.

Change your view to use the correct capitalization.

mlmcc
0
 
Jason YuAuthor Commented:
I changed the captions to "[SyCampusID]" , and relink the code with crystal report, but it's still not working.

rpt_AdStatusChanges_vw.fBeginDate, rpt_AdStatusChanges_vw.fReturnDate, rpt_AdStatusChanges_vw.SyCampusID,

I copied the original code and make it clear to see and attach the file here.
dbo.cst-adStuStatusChangesUpdate.txt
0
 
Jason YuAuthor Commented:
Is there some problem with the footer section.

the code from the original crystal report is as follows:

<SELECTION>
rpt_adStatusChanges_vw.NewSySchoolStatusID(SyStatusCategory:A;E;L;N;P;T;C;)
rpt_adStatusChanges_vw.PrevSySchoolStatusID(SySchoolStatus:A;E;L;N;P;T;C;)
rpt_adStatusChanges_vw.NewSySchoolStatusID(SySchoolStatus:A;E;L;N;P;T;C;)
rpt_adStatusChanges_vw.EffectiveDate()
rpt_adStatusChanges_vw.SyCampusID(SyCampusGrp)
rpt_adStatusChanges_vw.AdShiftID(AdShift)
rpt_adStatusChanges_vw.DateAdded()
rpt_adStatusChanges_vw.AdReasonID(AdReason)
rpt_adStatusChanges_vw.SySchoolStatusID(SySchoolStatus)

The new footer after my modification is as below:
<SELECTION>
cst_adStuStatusChangesUpdate_jy_vw.NewSySchoolStatusID(SyStatusCategory:A;E;L;N;P;T;C;)
cst_adStuStatusChangesUpdate_jy_vw.PrevSySchoolStatusID(SySchoolStatus:A;E;L;N;P;T;C;)
cst_adStuStatusChangesUpdate_jy_vw.NewSySchoolStatusID(SySchoolStatus:A;E;L;N;P;T;C;)
cst_adStuStatusChangesUpdate_jy_vw.EffectiveDate()
cst_adStuStatusChangesUpdate_jy_vw.SyCampusID(SyCampusGrp)
cst_adStuStatusChangesUpdate_jy_vw.AdShiftID(AdShift)
cst_adStuStatusChangesUpdate_jy_vw.DateAdded()
cst_adStuStatusChangesUpdate_jy_vw.AdReasonID(AdReason)
cst_adStuStatusChangesUpdate_jy_vw.SySchoolStatusID(SySchoolStatus)


I only changed the prefix in the footer in the new crystal report.  Any effect?  I notice when I run the report from the front, it doesn't give me the selection box as the old crystal report does. Please see the print screen for detail.
OldReportWorkingFineWithSelectio.jpg
NewReportIsNotWorkingFineWithSel.jpg
0
 
Jason YuAuthor Commented:
I modified the code as you suggested using alias.


USE [Live_45]
GO

/****** Object:  View [dbo].[cst_adStuStatusChangesUpdate_jy_vw]    Script Date: 09/05/2012 11:54:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[cst_adStuStatusChangesUpdate_jy_vw]
AS
SELECT     AD.BeginDate,
               AD.DateAdded,
               AD.Status,
               AD.SySchoolStatusDescrip,
               AD.StatusSort,
               AD.PrevSySchoolStatusID,
           AD.PrevStatus,
           AD.NewSySchoolStatusID,
           AD.NewStatDescrip,
           AD.SySchoolStatusID,
           AD.CurrentStatus,
           AD.EffectiveDate,
              CAST(AD.InternalComm AS varchar(899)) AS InternalComm, AD.ByUser, AD.Counter,
              AD.Category, AD.CategoryStatus, AD.Enrollments,
              AD.StudentName, AD.SSN, AD.enrollNum, AD.ApplDate,
              AD.StuNum, AD.SyStudentId, '            ' AS PrintID, AD.EnrollID,
              AD.StudentID, AD.Comment, AD.PgmVerCde, AD.ShiftDesc,
              AD.Campus, AD.DateStarted, AD.EnrollDescrip,
              AD.fBeginDate, AD.fReturnDate, AD.SyCampusID,
              AD.ProgramVersion, AD.CampusCode, AD.StudentNameEnroll,
              AD.Reason, AD.NewStatCategory, AD.StoredLDA,
              AD.StudentPhone, AD.StudentAddress, AD.StudentCity,
              AD.StudentState, AD.StudentCountry, AD.StudentZip,
              AD.NSLDSWithdrawalDate, AD.DropDate, dbo.SyStaff.SyStaffID, dbo.SyStaff.FirstName,
              dbo.SyStaff.LastName
FROM         dbo.rpt_AdStatusChanges_vw AD INNER JOIN
                      dbo.syStudent WITH (nolock) ON AD.SyStudentID = SyStudent.SyStudentID INNER JOIN
                      dbo.SyStaff ON dbo.syStudent.AmRepID = dbo.SyStaff.SyStaffID LEFT OUTER JOIN
                      dbo.AdEnroll WITH (nolock) ON adenroll.adenrollid = AD.enrollid
WHERE     (1 = 1)



GO
0
 
mlmccCommented:
Did that help?

mlmcc
0
 
Jason YuAuthor Commented:
In fact, No.

do you mind have a remote session and take a look, I have gotomeeting to hold a meeting.

thank you.
0
 
Jason YuAuthor Commented:
Hi, mlmmc:

I recreated the view file, it is partially working. The only problem is the three fields I added in the view don't show on the crystal report. When I run the select query based on this new view, it shows.


The code is as in the attachment.
NewView.txt
0
 
Jason YuAuthor Commented:
After I created a new view and try to run the report from front end, it gives me error;

"This field name is not known. " Please help, thank you very much.
The-field-name-is-unknow.jpg
0
 
mlmccCommented:
When you change a query/table/view you have to force Crystal to reread it

CLick DATABASE --> VERIFY DATABASE

mlmcc
0
 
Jason YuAuthor Commented:
I verified database as you instructed and tried to run the report. It still shows "This field name is not known. ", if I don't use those three newly added fields, the report works fine. That means the newly added fields have problem.

did you have a chance to look at the code. The newly added fields are from a different table instead from the old view.

I did a select query based on the new view from SQL Server management Studio, the result windows shows all the fields including the three newly added fields. But when I run the crystal report, it doesn't.

Thank you.
0
 
mlmccCommented:
Are those the Staff fields?

mlmcc
0
 
Jason YuAuthor Commented:
Yes, They are from SyStaff table. The working fields are from an old view.

Is this the problem: I can't create a new view based on an old view and a table?

Please advise.
0
 
Jason YuAuthor Commented:
Any Update, mlmcc, I hope you can give me some update. thank you.
0
 
Jason YuAuthor Commented:
I got the solution, it's the databse connectio problem. On the campusvue system, the front end report name should be the same as the procedure's name. I used different names, that created the problem. After I called the customer service, they solved this hard issue.

Hi Jason,

What are the new fields that you added to the view and are you modifying the cs5)adstustatuschanges_vw? The crystal report name and the view name have to match. If the report name is adtest.rpt the corresponding view would be adtest_vw.

Thanks,
Peter
0
 
mlmccCommented:
Excellent.  I doubt any of us would have known that or figured it out.

mlmcc
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now