Error when no recordset returned

I get the following error for five of my fields on my report when no
recordset is returned. They all happen to be items that are being pulled from
another table. So it seems that if there are no IDs to link to the other
table, I get this error. Can anyone tell me how I can get around this to show
a blank report instead of an error message? Thanks.
d:\reports\ProblemReport.rdl The value expression for the textbox
â'LocationNameâ' refers to the field â'LocationNameâ'. Report item expressions
can only refer to fields within the current data set scope or, if inside an
aggregate, the specified data set scope.Please disregard the previous message...apparently I was not too smart
earlier today :)
"Mike Collins" wrote:
> I get the following error for five of my fields on my report when no
> recordset is returned. They all happen to be items that are being pulled from
> another table. So it seems that if there are no IDs to link to the other
> table, I get this error. Can anyone tell me how I can get around this to show
> a blank report instead of an error message? Thanks.
> d:\reports\ProblemReport.rdl The value expression for the textbox
> â'LocationNameâ' refers to the field â'LocationNameâ'. Report item expressions
> can only refer to fields within the current data set scope or, if inside an
> aggregate, the specified data set scope.
>|||Hi Mike,
I am facing the same problem in one of my reports.
Can you please post the solution (or work-around) to do away with this error
message ?
Your help would be highly appreciated.
Regards,
Zayeem.
"Mike Collins" wrote:
> Please disregard the previous message...apparently I was not too smart
> earlier today :)
> "Mike Collins" wrote:
> > I get the following error for five of my fields on my report when no
> > recordset is returned. They all happen to be items that are being pulled from
> > another table. So it seems that if there are no IDs to link to the other
> > table, I get this error. Can anyone tell me how I can get around this to show
> > a blank report instead of an error message? Thanks.
> >
> > d:\reports\ProblemReport.rdl The value expression for the textbox
> > â'LocationNameâ' refers to the field â'LocationNameâ'. Report item expressions
> > can only refer to fields within the current data set scope or, if inside an
> > aggregate, the specified data set scope.
> >

Error when field in ORDER BY does not match SELECT DISTINCT fields

This error crept up in SQL 2005 and does not appear in earlier versions of
SQL Server. The below code looks like:
SELECT DISTINCT 'Test' As TestName, ...
FROM ...
ORDER BY 'Test'
I had to change the ORDER BY to the alias TestName to get is to compile and
run in SQL2005. Is there a SQL command I can run to prior to adding my view
to prevent these messages? Or do I lump it and change all occurrences to us
e
the alias in the ORDER BY?
Thanks for your help,
TomOn Mon, 20 Mar 2006 10:27:30 -0800, Tom Kelley <Tom
Kelley@.discussions.microsoft.com> wrote:

>This error crept up in SQL 2005 and does not appear in earlier versions of
>SQL Server. The below code looks like:
>SELECT DISTINCT 'Test' As TestName, ...
>FROM ...
>ORDER BY 'Test'
>I had to change the ORDER BY to the alias TestName to get is to compile and
>run in SQL2005.
Hi Tom,
SQL Server 2000 was very forgiving WRT what it allowed you to put in an
ORDER BY clause. That had some great advantages, but some divantages
as well, since it allowed you to write rather ambiguous things, and it
would often interpret it different from what you meant. For instance, if
you still have access to a SQL Server 2000 installation, run the code
below and try to explain the results.
CREATE TABLE Persons
(FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL
)
go
INSERT INTO Persons (FirstName, LastName)
SELECT 'Hugo', 'Kornelis'
UNION ALL
SELECT 'Tom', 'Kelly'
go
SELECT LastName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName, FirstName AS LastName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY Persons.FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY FirstName + ''
go
DROP TABLE Persons
go
Anyway, SQL Server 2005 is more strict (though not nearly as strict as
what the official ANSI standards for SQL allow <g> ). You might have to
change some ORDER BY clauses.

> Is there a SQL command I can run to prior to adding my view
>to prevent these messages? Or do I lump it and change all occurrences to u
se
>the alias in the ORDER BY?
The ORDER BY clause doesn't only accept the alias - it accepts column
names and expressions as well (though apparently not an "expression"
that is a string constant).
Though I personally fail to see ANY reason why you would want to include
a constant value in your ORDER BY list.
Hugo Kornelis, SQL Server MVPsql

Error Validating Formula for Column

Hi,
I have three fields APPLICANT_FIRST_NAME, APPLICANT_LAST_NAME and
APPLICANT_NAME
While designing table in sql enterprise manager I am inserting
"APPLICANT_FIRST_NAME & APPLICANT_LAST_NAME" for Formula Value of
APPLICANT_NAME field
But I am getting the following error
"Error Validating Formula for Column"
I need a field APPLICANT_NAME that has both values APPLICANT_FIRST_NAME and
APPLICANT_LAST_NAME in it
Can someone help me
Thanks
Ponnurangam
Hi
Your question is answered in .programming group. Don't multi-post.
"Ponnurangam" <ponnurangam@.trellisys.net> wrote in message
news:eSljuKsqEHA.3416@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have three fields APPLICANT_FIRST_NAME, APPLICANT_LAST_NAME and
> APPLICANT_NAME
> While designing table in sql enterprise manager I am inserting
> "APPLICANT_FIRST_NAME & APPLICANT_LAST_NAME" for Formula Value of
> APPLICANT_NAME field
> But I am getting the following error
> "Error Validating Formula for Column"
> I need a field APPLICANT_NAME that has both values APPLICANT_FIRST_NAME
and
> APPLICANT_LAST_NAME in it
> Can someone help me
> Thanks
> Ponnurangam
>
|||I get the same error even if use "+" instead of "&"
Any help is appreciated
Ponnurangam
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:<#lLB$NsqEHA.2732@.TK2MSFTNGP09.phx.gbl>...

> Hi

> Your question is answered in .programming group. Don't multi-post.

>

>

>

> "Ponnurangam" <ponnurangam@.trellisys.net> wrote in message

> news:eSljuKsqEHA.3416@.TK2MSFTNGP15.phx.gbl...
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]

> and
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]

>

>

Error Validating Formula for Column

Hi,
I have three fields APPLICANT_FIRST_NAME, APPLICANT_LAST_NAME and
APPLICANT_NAME
While designing table in sql enterprise manager I am inserting
"APPLICANT_FIRST_NAME & APPLICANT_LAST_NAME" for Formula Value of
APPLICANT_NAME field
But I am getting the following error
"Error Validating Formula for Column"
I need a field APPLICANT_NAME that has both values APPLICANT_FIRST_NAME and
APPLICANT_LAST_NAME in it
Can someone help me
Thanks
PonnurangamHi
Your question is answered in .programming group. Don't multi-post.
"Ponnurangam" <ponnurangam@.trellisys.net> wrote in message
news:eSljuKsqEHA.3416@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have three fields APPLICANT_FIRST_NAME, APPLICANT_LAST_NAME and
> APPLICANT_NAME
> While designing table in sql enterprise manager I am inserting
> "APPLICANT_FIRST_NAME & APPLICANT_LAST_NAME" for Formula Value of
> APPLICANT_NAME field
> But I am getting the following error
> "Error Validating Formula for Column"
> I need a field APPLICANT_NAME that has both values APPLICANT_FIRST_NAME
and
> APPLICANT_LAST_NAME in it
> Can someone help me
> Thanks
> Ponnurangam
>

Error Validating Any Check Constraint

Hey Guys,

I just this day started using SQL sever 2005. I created a database and

then created a table. Then I started adding some fields. I wanted to

add a check constraint to one of the fields called state but I keep

getting the same error. I right click on the field while editing the

table and select check constraint. I then click add on the check

constraint dialogue and in the expression caption I input the

following:

<code>

@.State In('CA', 'AZ', 'UT', 'CO')

</code>

I am using a book and have straight copied the above example from

the book. However when I input the check constraint I get the following

error;

"Error validating constraint 'ck_myfirstdatabase'

I have tried this with other fields and other types of check

constraints and I still get the same error. I have tried to delete the

database and recreate it. I have tried everything I can think of and I

cannot seem to get check constraints to work. I have no idea why I keep

getting this message. I have checked the

examples a

thousand times, the syntax is definately correct. This is getting

extremely annoying as I cannot continue unless I do this. I'm all out

of ideas. Can anyone please tell me

why it could not be working? Any ideas would be greatly appreciated.

hi,

i think this

@.State In('CA', 'AZ', 'UT', 'CO')

should be like this

State In('CA', 'AZ', 'UT', 'CO')

@.state is a varaible and constraints dont use it.

Anyway the best way to learn constraint is to use TSQL scripts and here's an example

create table mytable
(
chardate char(10),
charfloat char(5)
)
GO

ALTER TABLE MYTABLE
ADD
CONSTRAINT column_chardate_chk
CHECK
(
chardate like '[0-1][0-9]/[3-9][0-9]/[0-9][0-9][0-9][0-9]'
)
GO
ALTER TABLE MYTABLE
ADD
CONSTRAINT column_int_chk CHECK
(
charfloat like '[0-9][0-9].[0-9][0-9]'
)
GO

INSERT MYTABLE VALUES('01/01/2005','01.20') this will insert

GO
INSERT MYTABLE VALUES('01/01/2005','001.20') this will fail

SELECT * FROM MYTABLE