asked on

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

momi_sabag

what happens when you run the following query

select SyCampusID from cst_adStuStatusChangesUpdate_jy_vw

Mike McCracken

That would tend to indicate that particular column doesn't exist. Verify the spelling.
Capitalization may make a difference

mlmcc

Mike McCracken

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

Jason Yu

ASKER

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.

Mike McCracken

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

Jason Yu

ASKER

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

Jason Yu

ASKER

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

Jason Yu

ASKER

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

Mike McCracken

Did that help?

mlmcc

Jason Yu

ASKER

In fact, No.

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

thank you.

Jason Yu

ASKER

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

Jason Yu

ASKER

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

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jason Yu

ASKER

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.

Mike McCracken

Are those the Staff fields?

mlmcc

Jason Yu

ASKER

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.

Jason Yu

ASKER

Any Update, mlmcc, I hope you can give me some update. thank you.

Jason Yu

ASKER

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

Mike McCracken

Excellent. I doubt any of us would have known that or figured it out.

mlmcc

The multi-part identifier &quot;X&quot; could not be bound

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