SQL Server bit field is always false. Am I crazy?

I have a SQL Server 2000 record with a bit field that I've verified with
Enterprise Manager to be = 1 but no matter what I do Classic ASP thinks it's
zero.
"field = <%=rs.fields("myfield")%>
" results in nothing
"field = <%=cstr(rs.fields("myfield"))%>
" results in nothing
"field = <%=cint(rs.fields("myfield"))%>
" results in 0
"field = <%=cbool(rs.fields("myfield"))%>
" results in false

I've changed value from 0 to 1 several times. I get the record when I add
the "where myfield = 1" to my query condition.

This is driving me crazy. Any clues?

Re: SQL Server bit field is always false. Am I crazy?

Jon in Canby Or. wrote:
> I have a SQL Server 2000 record with a bit field that I've verified
> with Enterprise Manager to be = 1 but no matter what I do Classic ASP
> thinks it's zero.
> "field = <%=rs.fields("myfield")%>
" results in nothing
> "field = <%=cstr(rs.fields("myfield"))%>
" results in nothing
> "field = <%=cint(rs.fields("myfield"))%>
" results in 0
> "field = <%=cbool(rs.fields("myfield"))%>
" results in false
>
> I've changed value from 0 to 1 several times. I get the record when I
> add the "where myfield = 1" to my query condition.
>
> This is driving me crazy. Any clues?

We can only guess. Are you looking at the right record?

How about a repro. Here is my attempt to reproduce your problem. I ran
this script on my sql server:
USE [Test]
GO
CREATE TABLE [dbo].[boolTest](
[boolCol] [bit] NOT NULL CONSTRAINT [DF_boolTest_boolCol] DEFAULT
((1))
) ON [PRIMARY]
go
INSERT INTO [Test].[dbo].[boolTest]
([boolCol])
VALUES
(1)

Then I ran this code in a test asp page:
<%
dim cn, rs
set cn=createobject("adodb.connection")
cn.Open "provider=sqloledb;data source=clnsqldev;" & _
"initial catalog=test;user id=vbact;password=tcabv"
set rs=cn.Execute("select * from booltest",,1)
if not rs.eof then
response.Write "rs(0) contains """ & rs(0) & """"
end if
rs.close:cn.Close
%>

This is the result that appeared in the browser:

rs(0) contains "True"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SQL Server bit field is always false. Am I crazy?

I hear where you're coming from. I did verify I've got the right record. It
gets stranger...

I dropped debug Response.Write statement throughout the code. I did the
rs.Open on line 72. It was on line 420 that I checked the value of the bit
field. So I stuck a debug statement right after the Open and in several spots
throughout the code. In doing that all of a sudden the bit field started
working correctly. I deleted the debug statements and it stopped working. So
right after the Open I put in this code:
if rs.Fields("MyField") then
end if
and now the code all works correctly. Some kind of asp bug?

Cheers,
Jon


"Bob Barrows [MVP]" wrote:

> Jon in Canby Or. wrote:
> > I have a SQL Server 2000 record with a bit field that I've verified
> > with Enterprise Manager to be = 1 but no matter what I do Classic ASP
> > thinks it's zero.
> > "field = <%=rs.fields("myfield")%>
" results in nothing
> > "field = <%=cstr(rs.fields("myfield"))%>
" results in nothing
> > "field = <%=cint(rs.fields("myfield"))%>
" results in 0
> > "field = <%=cbool(rs.fields("myfield"))%>
" results in false
> >
> > I've changed value from 0 to 1 several times. I get the record when I
> > add the "where myfield = 1" to my query condition.
> >
> > This is driving me crazy. Any clues?
>
> We can only guess. Are you looking at the right record?
>
> How about a repro. Here is my attempt to reproduce your problem. I ran
> this script on my sql server:
> USE [Test]
> GO
> CREATE TABLE [dbo].[boolTest](
> [boolCol] [bit] NOT NULL CONSTRAINT [DF_boolTest_boolCol] DEFAULT
> ((1))
> ) ON [PRIMARY]
> go
> INSERT INTO [Test].[dbo].[boolTest]
> ([boolCol])
> VALUES
> (1)
>
> Then I ran this code in a test asp page:
> <%
> dim cn, rs
> set cn=createobject("adodb.connection")
> cn.Open "provider=sqloledb;data source=clnsqldev;" & _
> "initial catalog=test;user id=vbact;password=tcabv"
> set rs=cn.Execute("select * from booltest",,1)
> if not rs.eof then
> response.Write "rs(0) contains """ & rs(0) & """"
> end if
> rs.close:cn.Close
> %>
>
> This is the result that appeared in the browser:
>
> rs(0) contains "True"
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: SQL Server bit field is always false. Am I crazy?

Jon in Canby Or. wrote:
> I hear where you're coming from. I did verify I've got the right
> record. It gets stranger...
>
> I dropped debug Response.Write statement throughout the code. I did
> the rs.Open on line 72. It was on line 420 that I checked the value
> of the bit field. So I stuck a debug statement right after the Open
> and in several spots throughout the code. In doing that all of a
> sudden the bit field started working correctly. I deleted the debug
> statements and it stopped working. So right after the Open I put in
> this code:
> if rs.Fields("MyField") then
> end if
> and now the code all works correctly. Some kind of asp bug?
>

This is totally outside of my experience. It resembles the old ODBC bug
with TEXT fields (http://www.aspfaq.com/show.asp?id=2188) where you
needed to make them the last field in the select list in order to read
their value (this bug did not appear in the OLE DB provider). as far as
I know, that bug did not affect bit fields ...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.