problem with boolean field?

[alex]

I'm using ASP.db PRO to add and update some records in a SQL Server 7.0 database. Several of my tables have boolean fields. These are represented in ASP.db with dropdown menus consisting of True and False. However, whenever I try to edit a record in one of these tables, I get the error below. I suspect it may have something to do with the booleans because tables without booleans don't give me trouble. Here's one of the errors I'm getting:

Error # = 80040E14
Description = Invalid column name 'False'.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42S22
NativeError = 207

Here's the code I'm using:

<%
Set MyDb = Server.CreateObject(&#34;AspDB.Pro&#34
MyDb.dbDSN = strconnect
MyDb.dbUnit = 1101

MyDb.DBColor = &#34;3,auto,white&#34;
MyDb.dbGridTableTag = &#34;border=1 cellspacing=3 cellpadding=3&#34;
MyDb.dbFormTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.dbGridHideFlds = &#34;labid&#34;
MyDb.DbMode = &#34;Dual&#34;
MyDb.dbGridInc = 5

Mydb.dbEditFlds = &#34;labname,lablocation,labstate,labinfo,lablink, labcontact,labactive&#34;
MyDb.dbEditUpdateROFlds = &#34;labid&#34;

Mydb.dbEditParams = &#34;TableName=labels,BookMarkFlds=0,TableTag=Bord er=2&#34;

MyDb.dbSQL = &#34;Select * from labels&#34;

Mydb.dbNavigationItem=&#34;Next, Prev, Gridrow, Filter, add, update, edit, delete&#34;


MyDb.aspDBPro
%>

[Frank Kwong]

a column name called &#34;false&#34; ? Check your edit SQL by makeing a dump using dbDebug.

FK


------------
alex at 11/27/2001 8:43:10 AM

I&#39;m using ASP.db PRO to add and update some records in a SQL Server 7.0 database. Several of my tables have boolean fields. These are represented in ASP.db with dropdown menus consisting of True and False. However, whenever I try to edit a record in one of these tables, I get the error below. I suspect it may have something to do with the booleans because tables without booleans don&#39;t give me trouble. Here&#39;s one of the errors I&#39;m getting:

Error # = 80040E14
Description = Invalid column name &#39;False&#39;.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42S22
NativeError = 207

Here&#39;s the code I&#39;m using:

<%
Set MyDb = Server.CreateObject(&#34;AspDB.Pro&#34
MyDb.dbDSN = strconnect
MyDb.dbUnit = 1101

MyDb.DBColor = &#34;3,auto,white&#34;
MyDb.dbGridTableTag = &#34;border=1 cellspacing=3 cellpadding=3&#34;
MyDb.dbFormTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.dbGridHideFlds = &#34;labid&#34;
MyDb.DbMode = &#34;Dual&#34;
MyDb.dbGridInc = 5

Mydb.dbEditFlds = &#34;labname,lablocation,labstate,labinfo,lablink, labcontact,labactive&#34;
MyDb.dbEditUpdateROFlds = &#34;labid&#34;

Mydb.dbEditParams = &#34;TableName=labels,BookMarkFlds=0,TableTag=Bord er=2&#34;

MyDb.dbSQL = &#34;Select * from labels&#34;

Mydb.dbNavigationItem=&#34;Next, Prev, Gridrow, Filter, add, update, edit, delete&#34;


MyDb.aspDBPro
%>

[alex]

Frank,
The problem does indeed appear to be with the SQL generated by ASP.db. I did checked the edit SQL and when I try to do an update, this is the SQL generated:

---------------
UPDATE labels SET labname = &#39;Revelation Records&#39;, lablocation = &#39;Null&#39;, labstate = &#39;Null&#39;, labinfo = &#39;Null&#39;, lablink = &#39;www.revhq.com&#39;, labcontact = &#39;Null&#39;, labactive = True WHERE labid = 35
---------------

This is correct except for the &#34;labactive = True&#34; part. SQL Server will only accept a 0 or 1 for this value. In otherwords, the slightly modified statement below works:

---------------
UPDATE labels SET labname = &#39;Revelation Records&#39;, lablocation = &#39;Null&#39;, labstate = &#39;Null&#39;, labinfo = &#39;Null&#39;, lablink = &#39;www.revhq.com&#39;, labcontact = &#39;Null&#39;, labactive = 1 WHERE labid = 35
---------------


How can I get ASP.db to utilize a 1 or 0 instead of True or False?


------------
Frank Kwong at 11/27/2001 9:38:00 AM

a column name called &#34;false&#34; ? Check your edit SQL by makeing a dump using dbDebug.

FK


------------
alex at 11/27/2001 8:43:10 AM

I&#39;m using ASP.db PRO to add and update some records in a SQL Server 7.0 database. Several of my tables have boolean fields. These are represented in ASP.db with dropdown menus consisting of True and False. However, whenever I try to edit a record in one of these tables, I get the error below. I suspect it may have something to do with the booleans because tables without booleans don&#39;t give me trouble. Here&#39;s one of the errors I&#39;m getting:

Error # = 80040E14
Description = Invalid column name &#39;False&#39;.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42S22
NativeError = 207

Here&#39;s the code I&#39;m using:

<%
Set MyDb = Server.CreateObject(&#34;AspDB.Pro&#34
MyDb.dbDSN = strconnect
MyDb.dbUnit = 1101

MyDb.DBColor = &#34;3,auto,white&#34;
MyDb.dbGridTableTag = &#34;border=1 cellspacing=3 cellpadding=3&#34;
MyDb.dbFormTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.dbGridHideFlds = &#34;labid&#34;
MyDb.DbMode = &#34;Dual&#34;
MyDb.dbGridInc = 5

Mydb.dbEditFlds = &#34;labname,lablocation,labstate,labinfo,lablink, labcontact,labactive&#34;
MyDb.dbEditUpdateROFlds = &#34;labid&#34;

Mydb.dbEditParams = &#34;TableName=labels,BookMarkFlds=0,TableTag=Bord er=2&#34;

MyDb.dbSQL = &#34;Select * from labels&#34;

Mydb.dbNavigationItem=&#34;Next, Prev, Gridrow, Filter, add, update, edit, delete&#34;


MyDb.aspDBPro
%>

[alex]

Ok, forget it. I think I fixed the problem. I originally tried to do the following:

MyDb.dbBooltext=&#34;1,0,1,0&#34;

But it was still using &#39;True&#39; in the actual SQL query. I then went back an realized that i never explicity declared the database type, so I added the following line.

MyDb.dbDBType=&#34;SQL&#34;

The problem has since gone away. How stupid of me. I&#39;ve never used this line before and I guess it always worked with SQL Server anyway because I hadn&#39;t encountered any boolean fields before.


------------
alex at 11/27/2001 8:43:10 AM

I&#39;m using ASP.db PRO to add and update some records in a SQL Server 7.0 database. Several of my tables have boolean fields. These are represented in ASP.db with dropdown menus consisting of True and False. However, whenever I try to edit a record in one of these tables, I get the error below. I suspect it may have something to do with the booleans because tables without booleans don&#39;t give me trouble. Here&#39;s one of the errors I&#39;m getting:

Error # = 80040E14
Description = Invalid column name &#39;False&#39;.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42S22
NativeError = 207

Here&#39;s the code I&#39;m using:

<%
Set MyDb = Server.CreateObject(&#34;AspDB.Pro&#34
MyDb.dbDSN = strconnect
MyDb.dbUnit = 1101

MyDb.DBColor = &#34;3,auto,white&#34;
MyDb.dbGridTableTag = &#34;border=1 cellspacing=3 cellpadding=3&#34;
MyDb.dbFormTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.dbGridHideFlds = &#34;labid&#34;
MyDb.DbMode = &#34;Dual&#34;
MyDb.dbGridInc = 5

Mydb.dbEditFlds = &#34;labname,lablocation,labstate,labinfo,lablink, labcontact,labactive&#34;
MyDb.dbEditUpdateROFlds = &#34;labid&#34;

Mydb.dbEditParams = &#34;TableName=labels,BookMarkFlds=0,TableTag=Bord er=2&#34;

MyDb.dbSQL = &#34;Select * from labels&#34;

Mydb.dbNavigationItem=&#34;Next, Prev, Gridrow, Filter, add, update, edit, delete&#34;


MyDb.aspDBPro
%>

[Frank Kwong]

OK.. glad you got that under control.

FK


------------
alex at 11/27/2001 10:31:34 AM

Ok, forget it. I think I fixed the problem. I originally tried to do the following:

MyDb.dbBooltext=&#34;1,0,1,0&#34;

But it was still using &#39;True&#39; in the actual SQL query. I then went back an realized that i never explicity declared the database type, so I added the following line.

MyDb.dbDBType=&#34;SQL&#34;

The problem has since gone away. How stupid of me. I&#39;ve never used this line before and I guess it always worked with SQL Server anyway because I hadn&#39;t encountered any boolean fields before.


------------
alex at 11/27/2001 8:43:10 AM

I&#39;m using ASP.db PRO to add and update some records in a SQL Server 7.0 database. Several of my tables have boolean fields. These are represented in ASP.db with dropdown menus consisting of True and False. However, whenever I try to edit a record in one of these tables, I get the error below. I suspect it may have something to do with the booleans because tables without booleans don&#39;t give me trouble. Here&#39;s one of the errors I&#39;m getting:

Error # = 80040E14
Description = Invalid column name &#39;False&#39;.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42S22
NativeError = 207

Here&#39;s the code I&#39;m using:

<%
Set MyDb = Server.CreateObject(&#34;AspDB.Pro&#34
MyDb.dbDSN = strconnect
MyDb.dbUnit = 1101

MyDb.DBColor = &#34;3,auto,white&#34;
MyDb.dbGridTableTag = &#34;border=1 cellspacing=3 cellpadding=3&#34;
MyDb.dbFormTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.dbGridHideFlds = &#34;labid&#34;
MyDb.DbMode = &#34;Dual&#34;
MyDb.dbGridInc = 5

Mydb.dbEditFlds = &#34;labname,lablocation,labstate,labinfo,lablink, labcontact,labactive&#34;
MyDb.dbEditUpdateROFlds = &#34;labid&#34;

Mydb.dbEditParams = &#34;TableName=labels,BookMarkFlds=0,TableTag=Bord er=2&#34;

MyDb.dbSQL = &#34;Select * from labels&#34;

Mydb.dbNavigationItem=&#34;Next, Prev, Gridrow, Filter, add, update, edit, delete&#34;


MyDb.aspDBPro
%>

[antor]

I get the very same error as Alex but with TORNADO
and it couldn't be fixed with
.dbDBType="SQL"

This is my command that should work:
.dbEditUpdateFlds = "fi=0|ty=SELECTBOX|va=TF|te=TF"
Tornado puts true and false in the update statement for boolean fields, but sqlserver only accepts 1 or 0

By the way, the example at ASPDB (for filter)doesn't work either, it always returns Discontinued = 0:
http://www.aspdb.com/tornado/manual/Ex/Ex_booltext.aspx

Can anybody explain me, how to update boolean fields with tornado and sqlserver?

my current workaround is not satisfying, because it always defaults to false:
.dbDBType="SQL"
.dbBoolText = "DisplayTrue=ok| DisplayFalse=wrong|DropTrue=1| DropFalse=0"
.dbEditUpdateFlds = " fi=0|ty=SelectBox|va=boolnum|te=booltxt"
.dbCommonTables = "ind=boolnum,boollog,booltxt|Value=1,0~true,false~ ok,wrong"


thanks for any help

[Frank]

for classic ASP-db also look at editparams "BooleanAsBit=true".

Frank

[Frank]

if text=TF then dbBooltext takes control and use these values for text/value of the drop box. It is not a work around. Please explain the ->

>>..because it always defaults to false

as not correct. Can you define default values of the drop ?

FK

[Frank]

antor,
I looked at the Tornado code again.
Incorrect use of True Values in TF.
yes, you are correctand about having to run around to get to that.

It is now fixed. v12.13+

Will release patch version to support.

Frank

[antor]

Hi Frank,
>>Can you define default values of the drop ?
No, it's due to the use of booltext and commontables, that the update drop always shows the content of "DisplayTrue" and not the current status of the SQL bit-field.

I hope once the logic field handling with SQL is fixed and I can use TF, the update dropbox is initialized with the actual value of the bit-field

>>It is now fixed. v12.13+
>>Will release patch version to support.

please send me a note when it's released
for the Tornado-Gold version, with download link

Thanks

[John]

Hi,

Please email support@aspdb.com and include your ID file and request the latest version.

We'll get it right out to you.

Thanks,
John