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...