A SqlDataReader is returning an int, when it should be returning a tinyint

I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:

SELECT TreatmentStatusFROM vwReferralWithAdmissionDischarge
WHERE ClientNumber = 138238AND CaseNumber = 1AND ProviderNumber = 89

The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.

Why?

If you are just reteieving one value you might want to use ExecuteScalar which is faster and has less overhead than ExecuteReader.

|||

Try this:

int numericValue = System.Convert.ToInt32(yourDataReader.GetByte(0));

Cheers

|||

humormuch:

Try this:

int numericValue = System.Convert.ToInt32(yourDataReader.GetByte(0));

Cheers

I forgot to mention, in my original post, that I was using the GetByte() method of the SqlDataReader object, but that raised the following error message:

"Specified cast is not valid"

That's why I brought up the whole thing about knowing that the column was atinyint, but that the SqlDataReader in my ASP.NET 2.0 page is returning aint value instead, which I simply donot understand. Here is the relevant code snippet:

Dim sbAs StringBuilder =New StringBuilder("SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge ")sb.Append(String.Format("WHERE ClientNumber = {0} ", lClientNumber))sb.Append(String.Format("AND CaseNumber = {0} ", byCaseNumber))sb.Append(String.Format("AND ProviderNumber = {0}", nProviderNumber))Dim cmCheckTreatmentStatusAs SqlCommand =New SqlCommand(sb.ToString(), cn)cmCheckTreatmentStatus.CommandType = CommandType.TextDim sdrCheckTreatmentStatusAs SqlDataReader = cm.ExecuteReader()sdrCheckTreatmentStatus.Read()If sdrCheckTreatmentStatus.IsDBNull(0)Then'NOOPElseDim byTreatmentStatusAs Byte = sdrCheckTreatmentStatus.GetByte(0)'other stuff occurs hereEnd If

It is the line "Dim byTreatmentStatusAs Byte = sdrCheckTreatmentStatus.GetByte(0)" which is raising the error.

|||

Never mind, I found the mistake. It was a stupid mistake on my part. I had created a SqlCommand calledcmCheckTreatmentStatus, and then ran the ExecuteReader() method on another SqlCommand I defined earlier, calledcm.

I'm sorry everyone.

|||

DoctorWho:

Never mind, I found the mistake. It was a stupid mistake on my part. I had created a SqlCommand calledcmCheckTreatmentStatus, and then ran the ExecuteReader() method on another SqlCommand I defined earlier, calledcm.

I'm sorry everyone.

No problem. I remember I have done that quite a few times...

A question on @@IDENTITY

There is a stored procedure in our partner's application on MS SQL 2005 server like this(simplified):

Create Proc sp_SubmitData

{

@.data int

@.ID int OUTPUT

}

AS

BEGIN TRAN

INSERT INTO dataTable (data) VALUES(@.data)

-- Check @.@.ERROR

SET @.ID=@.@.IDENTITY

COMMIT TRAN

RETURN 0

In most cases @.ID returns the primay key of newly inserted row in dataTable. But in some cases (randomly, about 1/20 chances) @.ID returns a number we cannot figure out where it comes on the condition of a new row has been inserted into dataTable successfully. There is no trigger on dataTable. Is there any other chance that @.@.IDENTITY is refreshed by other sources between INSERT and SET sentence?

Chester

Try the SCOPE_IDENTITY function instead of @.@.identity; read the section that discusses both in books online. The short answer to your question can @.@.identity come from another source is "yes".|||

Thanks for your suggestion. Yesterday I browsed a lot on blogs and online books and have known the issue. But the problem is that there is a policy in our partner's company that the database is sealed. Only in this November can it be updated in the new roll out! It involves a lot of sites.

Chester

|||Unfortunately, @.@.identity has a checkered history. It has bitten many.

a query using GROUP BY or?

Is there a simple way to do the following?
The database table has many records, each record has its own unique RecordID
(PK, int), some of the records can have one text field like an intenrifier
(SomeID) with the same value. A simplified schema is looking like this:
RecordID SomeID Action
1 134 2
2 123 2
3 1243 2
4 134 1
5 1ytr 2
6 1fgh 2
7 1243 1
8 hgf 2
9 b4rfg 2
I need to assign the value '1' or '2' to the Action field so that if we
order the whole list by the RecordID and then group it by the SomeID field,
the first record in each group (with the same SomeID field) should have
Action=2, all next entries inside each group should have Action=1. All
records without duplicates should have Action=2.
I can set Action=2 to all records, it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records?
Thanks,
Just D.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
grp_id CHAR(5) NOT NULL,
action_code INTEGER DEFAULT 2 NOT NULL
CHECK (action_code IN (1,2)));
Action=2, all next entries inside each group should have Action=1. All
records [sic] without duplicates should have Action=2. I can set
Action=2 to all records [sic], it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records [sic] ? <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. A normalized table should not have redundant duplicates.
I am going to guess that this is what you want:
UPDATE Foobar
SET action_code
= CASE WHEN foo_id
< (SELECT MAX(foo_id)
FROM Foobar AS F1
WHERE F1.grp_id = Foobar.grp_id)
THEN 1 ELSE 2 END;|||Try this one:
update tbl set
Action =
case when RecordID = (select min(RecordID) from tbl as t where
t.SomeID = tb.SomeID)
then 2
else 1
end|||HI,
Excellent! That's a very good idea! Thanks!
Just D.
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127474970.840245.308070@.g44g2000cwa.googlegroups.com...
> Try this one:
> update tbl set
> Action =
> case when RecordID = (select min(RecordID) from tbl as t where
> t.SomeID = tb.SomeID)
> then 2
> else 1
> end
>

A problem of query between MS sql and Sybase

There is a query as following.....

-Creat a test table
if object_id('tbTest') is not null
drop table tbTest
GO
create table tbTest(m_date int, m_name varchar(10),m_hour int)
insert tbTest
select 1991,'Jack',1 union all
select 1991,'Jack',1 union all
select 1991,'Tom',1 union all
select 1992,'Jack',1 union all
select 1992,'Bob',1

-create the query
declare @.sql varchar(8000)
set @.sql = 'select '
select @.sql = @.sql + ',' + m_name + '=sum(case m_name when ''' + m_name + ''' then m_hour else 0 end)'
from tbTest group by m_name
EXEC(@.sql + ' from tbTest group by m_date')

the result run on MS sql server

/*result
m_date Bob Tom Jack
-- -- -- --
1991 0 1 2
1992 1 0 1
*/

But the result in Sybase is

m_date Bob
-- --
1991 0
1992 1

Why?How to modify the query to get the same result just as it on MS sql server.

You might posting this in a Sysbase related forum.