Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)

[Stan Muse]

I am converting my application from access2000 to SQL2000. One of my ASPDB pages is giving the following error when selecting a record to update in SQL.

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

The field that this is occurring on was binary in access, and is defined as bit in SQL. My take on the error is that SQL isn't looking at the correct field since it says the "type equals varchar".

Here is a copy of the code:

<%
Set MyDb = Server.CreateObject(&#34;Asp.db&#34
MyDb.dbUnit = 101
MYDB.dbDBType=&#34;SQL&#34;
MYDB.dbDSN = &#34;provider=SQLOLEDB; data source=DOERSSQL1DOERSSQLTEST1; Initial Catalog = doerssql; User ID= sa&#34;
mydb.dbRSCursor=1
mydb.dbmode=&#34;BOTH&#34;
mydb.dbBoolText=&#34;Yes, No, Yes, No&#34;
Mydb.dbColor=&#34;true,lightblue,true,gold,,,,true &#34;
mydb.dbGridTableTag = &#34;Border=2&#34;
mydb.dbNavigation = &#34;both&#34;
LocalFacilityID = session(&#34;facilityid&#34
mydb.dbNavigationitem= &#34;Top,Prev,Next,Bottom, update, gridrow, filter&#34;
mydb.dbOptions = &#34;GridInitSortDESC =True&#34;
mydb.dbEditParams = &#34;tablename=COSTCENT, InputSize=80, BookMarkFlds=0, tabletag=border=1, RecordScope=single, size=5x60&#34;

STRSQL = &#34;SELECT COS_PK, COS_CostCodeNumber, COS_CostCodeDescription, COS_Active, COS_SupervisorEMPFK, COS_SupervisorEmailAddress,&#34;
STRSQL = STRSQL & &#34; COS_FacilityFACFK FROM CostCent&#34;
strSQL = strSQL & &#34; WHERE Cos_FACILITYFACFK = &#34; & LocalFacilityID
mydb.dbSQL = strSQL

MYDB.dbDisplayLookUpList = &#34;(;|)Cos_SupervisorEMPFK ||Select EMP_PK, EMP_SocialSecurityNumber From Employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID
mydb.dbNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
mydb.dbFormNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
MYDB.dbEditFlds = &#34;1,2,3,4,5,6&#34;
EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;
MYDB.dbEditDropFlds = EDF
MYDB.dbFilterFlds = &#34;1&#34;
MYDB.dbUserLocalText=zDrop & &#34;,none;&#34;
MYDB.dbGridHideFlds = &#34;0&#34;
MYDB.dbEditUpdateROFlds = &#34;,1,2,3,6&#34;
MYDB.dbSelectFROM = TRUE
MYDB.dbGridInc = 20
mydb.dbNavigationIcon = &#34;Std&#34;
mydb.dbImageDir = &#34;images/images/&#34;
MyDb.aspDB &#39;Display the data!
JUSTREAD = MYDB.dbRecordCount
%>

[Frank Kwong]

explain -> &#34;binary in access, and is defined as bit in SQL.&#34;
what field type is that - type (Boolean=11) will be set as 1/0.

FK


------------
Stan Muse at 3/22/2002 6:35:51 PM

I am converting my application from access2000 to SQL2000. One of my ASPDB pages is giving the following error when selecting a record to update in SQL.

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

The field that this is occurring on was binary in access, and is defined as bit in SQL. My take on the error is that SQL isn&#39;t looking at the correct field since it says the &#34;type equals varchar&#34;.

Here is a copy of the code:

<%
Set MyDb = Server.CreateObject(&#34;Asp.db&#34
MyDb.dbUnit = 101
MYDB.dbDBType=&#34;SQL&#34;
MYDB.dbDSN = &#34;provider=SQLOLEDB; data source=DOERSSQL1DOERSSQLTEST1; Initial Catalog = doerssql; User ID= sa&#34;
mydb.dbRSCursor=1
mydb.dbmode=&#34;BOTH&#34;
mydb.dbBoolText=&#34;Yes, No, Yes, No&#34;
Mydb.dbColor=&#34;true,lightblue,true,gold,,,,true &#34;
mydb.dbGridTableTag = &#34;Border=2&#34;
mydb.dbNavigation = &#34;both&#34;
LocalFacilityID = session(&#34;facilityid&#34
mydb.dbNavigationitem= &#34;Top,Prev,Next,Bottom, update, gridrow, filter&#34;
mydb.dbOptions = &#34;GridInitSortDESC =True&#34;
mydb.dbEditParams = &#34;tablename=COSTCENT, InputSize=80, BookMarkFlds=0, tabletag=border=1, RecordScope=single, size=5x60&#34;

STRSQL = &#34;SELECT COS_PK, COS_CostCodeNumber, COS_CostCodeDescription, COS_Active, COS_SupervisorEMPFK, COS_SupervisorEmailAddress,&#34;
STRSQL = STRSQL & &#34; COS_FacilityFACFK FROM CostCent&#34;
strSQL = strSQL & &#34; WHERE Cos_FACILITYFACFK = &#34; & LocalFacilityID
mydb.dbSQL = strSQL

MYDB.dbDisplayLookUpList = &#34;(;|)Cos_SupervisorEMPFK ||Select EMP_PK, EMP_SocialSecurityNumber From Employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID
mydb.dbNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
mydb.dbFormNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
MYDB.dbEditFlds = &#34;1,2,3,4,5,6&#34;
EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;
MYDB.dbEditDropFlds = EDF
MYDB.dbFilterFlds = &#34;1&#34;
MYDB.dbUserLocalText=zDrop & &#34;,none;&#34;
MYDB.dbGridHideFlds = &#34;0&#34;
MYDB.dbEditUpdateROFlds = &#34;,1,2,3,6&#34;
MYDB.dbSelectFROM = TRUE
MYDB.dbGridInc = 20
mydb.dbNavigationIcon = &#34;Std&#34;
mydb.dbImageDir = &#34;images/images/&#34;
MyDb.aspDB &#39;Display the data!
JUSTREAD = MYDB.dbRecordCount
%>

Yes it is Boolean, in Access it is -1 and 0, in SQL it is 1 and 0.

------------
Frank Kwong at 3/26/2002 5:31:07 AM

explain -> &#34;binary in access, and is defined as bit in SQL.&#34;
what field type is that - type (Boolean=11) will be set as 1/0.

FK


------------
Stan Muse at 3/22/2002 6:35:51 PM

I am converting my application from access2000 to SQL2000. One of my ASPDB pages is giving the following error when selecting a record to update in SQL.

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

The field that this is occurring on was binary in access, and is defined as bit in SQL. My take on the error is that SQL isn&#39;t looking at the correct field since it says the &#34;type equals varchar&#34;.

Here is a copy of the code:

<%
Set MyDb = Server.CreateObject(&#34;Asp.db&#34
MyDb.dbUnit = 101
MYDB.dbDBType=&#34;SQL&#34;
MYDB.dbDSN = &#34;provider=SQLOLEDB; data source=DOERSSQL1DOERSSQLTEST1; Initial Catalog = doerssql; User ID= sa&#34;
mydb.dbRSCursor=1
mydb.dbmode=&#34;BOTH&#34;
mydb.dbBoolText=&#34;Yes, No, Yes, No&#34;
Mydb.dbColor=&#34;true,lightblue,true,gold,,,,true &#34;
mydb.dbGridTableTag = &#34;Border=2&#34;
mydb.dbNavigation = &#34;both&#34;
LocalFacilityID = session(&#34;facilityid&#34
mydb.dbNavigationitem= &#34;Top,Prev,Next,Bottom, update, gridrow, filter&#34;
mydb.dbOptions = &#34;GridInitSortDESC =True&#34;
mydb.dbEditParams = &#34;tablename=COSTCENT, InputSize=80, BookMarkFlds=0, tabletag=border=1, RecordScope=single, size=5x60&#34;

STRSQL = &#34;SELECT COS_PK, COS_CostCodeNumber, COS_CostCodeDescription, COS_Active, COS_SupervisorEMPFK, COS_SupervisorEmailAddress,&#34;
STRSQL = STRSQL & &#34; COS_FacilityFACFK FROM CostCent&#34;
strSQL = strSQL & &#34; WHERE Cos_FACILITYFACFK = &#34; & LocalFacilityID
mydb.dbSQL = strSQL

MYDB.dbDisplayLookUpList = &#34;(;|)Cos_SupervisorEMPFK ||Select EMP_PK, EMP_SocialSecurityNumber From Employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID
mydb.dbNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
mydb.dbFormNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
MYDB.dbEditFlds = &#34;1,2,3,4,5,6&#34;
EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;
MYDB.dbEditDropFlds = EDF
MYDB.dbFilterFlds = &#34;1&#34;
MYDB.dbUserLocalText=zDrop & &#34;,none;&#34;
MYDB.dbGridHideFlds = &#34;0&#34;
MYDB.dbEditUpdateROFlds = &#34;,1,2,3,6&#34;
MYDB.dbSelectFROM = TRUE
MYDB.dbGridInc = 20
mydb.dbNavigationIcon = &#34;Std&#34;
mydb.dbImageDir = &#34;images/images/&#34;
MyDb.aspDB &#39;Display the data!
JUSTREAD = MYDB.dbRecordCount
%>

[Stan]

This is the line of code that is blowing up, specifically &#34;WHERE EMP_ACTIVE &&#34;

EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;



------------
Stan Muse at 3/22/2002 6:35:51 PM

I am converting my application from access2000 to SQL2000. One of my ASPDB pages is giving the following error when selecting a record to update in SQL.

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

The field that this is occurring on was binary in access, and is defined as bit in SQL. My take on the error is that SQL isn&#39;t looking at the correct field since it says the &#34;type equals varchar&#34;.

Here is a copy of the code:

<%
Set MyDb = Server.CreateObject(&#34;Asp.db&#34
MyDb.dbUnit = 101
MYDB.dbDBType=&#34;SQL&#34;
MYDB.dbDSN = &#34;provider=SQLOLEDB; data source=DOERSSQL1DOERSSQLTEST1; Initial Catalog = doerssql; User ID= sa&#34;
mydb.dbRSCursor=1
mydb.dbmode=&#34;BOTH&#34;
mydb.dbBoolText=&#34;Yes, No, Yes, No&#34;
Mydb.dbColor=&#34;true,lightblue,true,gold,,,,true &#34;
mydb.dbGridTableTag = &#34;Border=2&#34;
mydb.dbNavigation = &#34;both&#34;
LocalFacilityID = session(&#34;facilityid&#34
mydb.dbNavigationitem= &#34;Top,Prev,Next,Bottom, update, gridrow, filter&#34;
mydb.dbOptions = &#34;GridInitSortDESC =True&#34;
mydb.dbEditParams = &#34;tablename=COSTCENT, InputSize=80, BookMarkFlds=0, tabletag=border=1, RecordScope=single, size=5x60&#34;

STRSQL = &#34;SELECT COS_PK, COS_CostCodeNumber, COS_CostCodeDescription, COS_Active, COS_SupervisorEMPFK, COS_SupervisorEmailAddress,&#34;
STRSQL = STRSQL & &#34; COS_FacilityFACFK FROM CostCent&#34;
strSQL = strSQL & &#34; WHERE Cos_FACILITYFACFK = &#34; & LocalFacilityID
mydb.dbSQL = strSQL

MYDB.dbDisplayLookUpList = &#34;(;|)Cos_SupervisorEMPFK ||Select EMP_PK, EMP_SocialSecurityNumber From Employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID
mydb.dbNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
mydb.dbFormNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
MYDB.dbEditFlds = &#34;1,2,3,4,5,6&#34;
EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;
MYDB.dbEditDropFlds = EDF
MYDB.dbFilterFlds = &#34;1&#34;
MYDB.dbUserLocalText=zDrop & &#34;,none;&#34;
MYDB.dbGridHideFlds = &#34;0&#34;
MYDB.dbEditUpdateROFlds = &#34;,1,2,3,6&#34;
MYDB.dbSelectFROM = TRUE
MYDB.dbGridInc = 20
mydb.dbNavigationIcon = &#34;Std&#34;
mydb.dbImageDir = &#34;images/images/&#34;
MyDb.aspDB &#39;Display the data!
JUSTREAD = MYDB.dbRecordCount
%>

[John]

Hi Stan,

Can you change that &#34;WHERE EMP_ACTIVE&#34; to &#34;WHERE EMP_ACTIVE = 1&#34; (or TRUE or something?)

I&#39;d reduce that down to that one WHERE EMP_ACTIVE condition and get it working and then build back up from there. I think that could be where your problem is.

John

------------
Stan at 4/9/2002 7:50:10 PM

This is the line of code that is blowing up, specifically &#34;WHERE EMP_ACTIVE &&#34;

EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;



------------
Stan Muse at 3/22/2002 6:35:51 PM

I am converting my application from access2000 to SQL2000. One of my ASPDB pages is giving the following error when selecting a record to update in SQL.

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

The field that this is occurring on was binary in access, and is defined as bit in SQL. My take on the error is that SQL isn&#39;t looking at the correct field since it says the &#34;type equals varchar&#34;.

Here is a copy of the code:

<%
Set MyDb = Server.CreateObject(&#34;Asp.db&#34
MyDb.dbUnit = 101
MYDB.dbDBType=&#34;SQL&#34;
MYDB.dbDSN = &#34;provider=SQLOLEDB; data source=DOERSSQL1DOERSSQLTEST1; Initial Catalog = doerssql; User ID= sa&#34;
mydb.dbRSCursor=1
mydb.dbmode=&#34;BOTH&#34;
mydb.dbBoolText=&#34;Yes, No, Yes, No&#34;
Mydb.dbColor=&#34;true,lightblue,true,gold,,,,true &#34;
mydb.dbGridTableTag = &#34;Border=2&#34;
mydb.dbNavigation = &#34;both&#34;
LocalFacilityID = session(&#34;facilityid&#34
mydb.dbNavigationitem= &#34;Top,Prev,Next,Bottom, update, gridrow, filter&#34;
mydb.dbOptions = &#34;GridInitSortDESC =True&#34;
mydb.dbEditParams = &#34;tablename=COSTCENT, InputSize=80, BookMarkFlds=0, tabletag=border=1, RecordScope=single, size=5x60&#34;

STRSQL = &#34;SELECT COS_PK, COS_CostCodeNumber, COS_CostCodeDescription, COS_Active, COS_SupervisorEMPFK, COS_SupervisorEmailAddress,&#34;
STRSQL = STRSQL & &#34; COS_FacilityFACFK FROM CostCent&#34;
strSQL = strSQL & &#34; WHERE Cos_FACILITYFACFK = &#34; & LocalFacilityID
mydb.dbSQL = strSQL

MYDB.dbDisplayLookUpList = &#34;(;|)Cos_SupervisorEMPFK ||Select EMP_PK, EMP_SocialSecurityNumber From Employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID
mydb.dbNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
mydb.dbFormNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
MYDB.dbEditFlds = &#34;1,2,3,4,5,6&#34;
EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;
MYDB.dbEditDropFlds = EDF
MYDB.dbFilterFlds = &#34;1&#34;
MYDB.dbUserLocalText=zDrop & &#34;,none;&#34;
MYDB.dbGridHideFlds = &#34;0&#34;
MYDB.dbEditUpdateROFlds = &#34;,1,2,3,6&#34;
MYDB.dbSelectFROM = TRUE
MYDB.dbGridInc = 20
mydb.dbNavigationIcon = &#34;Std&#34;
mydb.dbImageDir = &#34;images/images/&#34;
MyDb.aspDB &#39;Display the data!
JUSTREAD = MYDB.dbRecordCount
%>

[Stan]

Hi John,
Well now I&#39;m really confused, I removed the suspect statement &#34;WHERE EMP_ACTIVE&#34; completely and still get this error:

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

I can&#39;t see where my code is invoking an operator or specifying data type. The code will bring data into the grid without error, it is when I try to edit that the error occurs.Maybe the question I should be asking is how can i make sure the operator matches the data type?


------------
John at 4/10/2002 4:11:22 AM


Hi Stan,

Can you change that &#34;WHERE EMP_ACTIVE&#34; to &#34;WHERE EMP_ACTIVE = 1&#34; (or TRUE or something?)

I&#39;d reduce that down to that one WHERE EMP_ACTIVE condition and get it working and then build back up from there. I think that could be where your problem is.

John

------------
Stan at 4/9/2002 7:50:10 PM

This is the line of code that is blowing up, specifically &#34;WHERE EMP_ACTIVE &&#34;

EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;



------------
Stan Muse at 3/22/2002 6:35:51 PM

I am converting my application from access2000 to SQL2000. One of my ASPDB pages is giving the following error when selecting a record to update in SQL.

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

The field that this is occurring on was binary in access, and is defined as bit in SQL. My take on the error is that SQL isn&#39;t looking at the correct field since it says the &#34;type equals varchar&#34;.

Here is a copy of the code:

<%
Set MyDb = Server.CreateObject(&#34;Asp.db&#34
MyDb.dbUnit = 101
MYDB.dbDBType=&#34;SQL&#34;
MYDB.dbDSN = &#34;provider=SQLOLEDB; data source=DOERSSQL1DOERSSQLTEST1; Initial Catalog = doerssql; User ID= sa&#34;
mydb.dbRSCursor=1
mydb.dbmode=&#34;BOTH&#34;
mydb.dbBoolText=&#34;Yes, No, Yes, No&#34;
Mydb.dbColor=&#34;true,lightblue,true,gold,,,,true &#34;
mydb.dbGridTableTag = &#34;Border=2&#34;
mydb.dbNavigation = &#34;both&#34;
LocalFacilityID = session(&#34;facilityid&#34
mydb.dbNavigationitem= &#34;Top,Prev,Next,Bottom, update, gridrow, filter&#34;
mydb.dbOptions = &#34;GridInitSortDESC =True&#34;
mydb.dbEditParams = &#34;tablename=COSTCENT, InputSize=80, BookMarkFlds=0, tabletag=border=1, RecordScope=single, size=5x60&#34;

STRSQL = &#34;SELECT COS_PK, COS_CostCodeNumber, COS_CostCodeDescription, COS_Active, COS_SupervisorEMPFK, COS_SupervisorEmailAddress,&#34;
STRSQL = STRSQL & &#34; COS_FacilityFACFK FROM CostCent&#34;
strSQL = strSQL & &#34; WHERE Cos_FACILITYFACFK = &#34; & LocalFacilityID
mydb.dbSQL = strSQL

MYDB.dbDisplayLookUpList = &#34;(;|)Cos_SupervisorEMPFK ||Select EMP_PK, EMP_SocialSecurityNumber From Employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID
mydb.dbNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
mydb.dbFormNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
MYDB.dbEditFlds = &#34;1,2,3,4,5,6&#34;
EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;
MYDB.dbEditDropFlds = EDF
MYDB.dbFilterFlds = &#34;1&#34;
MYDB.dbUserLocalText=zDrop & &#34;,none;&#34;
MYDB.dbGridHideFlds = &#34;0&#34;
MYDB.dbEditUpdateROFlds = &#34;,1,2,3,6&#34;
MYDB.dbSelectFROM = TRUE
MYDB.dbGridInc = 20
mydb.dbNavigationIcon = &#34;Std&#34;
mydb.dbImageDir = &#34;images/images/&#34;
MyDb.aspDB &#39;Display the data!
JUSTREAD = MYDB.dbRecordCount
%>

[Stan Muse]

The real problem is with the part of the statement &#34;& &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME &#34; where I retrieve the name values for display together. It appears that ASPDB parses this in a way that msSQL2000 doesn&#39;t like but which works in Access2000.

------------
John at 4/10/2002 4:11:22 AM


Hi Stan,

Can you change that &#34;WHERE EMP_ACTIVE&#34; to &#34;WHERE EMP_ACTIVE = 1&#34; (or TRUE or something?)

I&#39;d reduce that down to that one WHERE EMP_ACTIVE condition and get it working and then build back up from there. I think that could be where your problem is.

John

------------
Stan at 4/9/2002 7:50:10 PM

This is the line of code that is blowing up, specifically &#34;WHERE EMP_ACTIVE &&#34;

EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;



------------
Stan Muse at 3/22/2002 6:35:51 PM

I am converting my application from access2000 to SQL2000. One of my ASPDB pages is giving the following error when selecting a record to update in SQL.

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

The field that this is occurring on was binary in access, and is defined as bit in SQL. My take on the error is that SQL isn&#39;t looking at the correct field since it says the &#34;type equals varchar&#34;.

Here is a copy of the code:

<%
Set MyDb = Server.CreateObject(&#34;Asp.db&#34
MyDb.dbUnit = 101
MYDB.dbDBType=&#34;SQL&#34;
MYDB.dbDSN = &#34;provider=SQLOLEDB; data source=DOERSSQL1DOERSSQLTEST1; Initial Catalog = doerssql; User ID= sa&#34;
mydb.dbRSCursor=1
mydb.dbmode=&#34;BOTH&#34;
mydb.dbBoolText=&#34;Yes, No, Yes, No&#34;
Mydb.dbColor=&#34;true,lightblue,true,gold,,,,true &#34;
mydb.dbGridTableTag = &#34;Border=2&#34;
mydb.dbNavigation = &#34;both&#34;
LocalFacilityID = session(&#34;facilityid&#34
mydb.dbNavigationitem= &#34;Top,Prev,Next,Bottom, update, gridrow, filter&#34;
mydb.dbOptions = &#34;GridInitSortDESC =True&#34;
mydb.dbEditParams = &#34;tablename=COSTCENT, InputSize=80, BookMarkFlds=0, tabletag=border=1, RecordScope=single, size=5x60&#34;

STRSQL = &#34;SELECT COS_PK, COS_CostCodeNumber, COS_CostCodeDescription, COS_Active, COS_SupervisorEMPFK, COS_SupervisorEmailAddress,&#34;
STRSQL = STRSQL & &#34; COS_FacilityFACFK FROM CostCent&#34;
strSQL = strSQL & &#34; WHERE Cos_FACILITYFACFK = &#34; & LocalFacilityID
mydb.dbSQL = strSQL

MYDB.dbDisplayLookUpList = &#34;(;|)Cos_SupervisorEMPFK ||Select EMP_PK, EMP_SocialSecurityNumber From Employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID
mydb.dbNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
mydb.dbFormNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
MYDB.dbEditFlds = &#34;1,2,3,4,5,6&#34;
EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;
MYDB.dbEditDropFlds = EDF
MYDB.dbFilterFlds = &#34;1&#34;
MYDB.dbUserLocalText=zDrop & &#34;,none;&#34;
MYDB.dbGridHideFlds = &#34;0&#34;
MYDB.dbEditUpdateROFlds = &#34;,1,2,3,6&#34;
MYDB.dbSelectFROM = TRUE
MYDB.dbGridInc = 20
mydb.dbNavigationIcon = &#34;Std&#34;
mydb.dbImageDir = &#34;images/images/&#34;
MyDb.aspDB &#39;Display the data!
JUSTREAD = MYDB.dbRecordCount
%>

[John]

Hi Stan,

I wonder what it&#39;s executing when you try and edit. If you set x.dbDebug=101 it will show the SQL that ASPdb is executing. That might give you a good clue as to what the problem is.

Also, is it a problem when you go &#34;into&#34; edit mode, or when you click the submit button to make the change. Finally, is it in Add and Update or just one of those?

Don&#39;t forget to simply the program down to 10 lines or less to show the problem.

Thanks,
John



------------
Stan at 4/10/2002 11:43:19 AM

Hi John,
Well now I&#39;m really confused, I removed the suspect statement &#34;WHERE EMP_ACTIVE&#34; completely and still get this error:

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

I can&#39;t see where my code is invoking an operator or specifying data type. The code will bring data into the grid without error, it is when I try to edit that the error occurs.Maybe the question I should be asking is how can i make sure the operator matches the data type?


------------
John at 4/10/2002 4:11:22 AM


Hi Stan,

Can you change that &#34;WHERE EMP_ACTIVE&#34; to &#34;WHERE EMP_ACTIVE = 1&#34; (or TRUE or something?)

I&#39;d reduce that down to that one WHERE EMP_ACTIVE condition and get it working and then build back up from there. I think that could be where your problem is.

John

------------
Stan at 4/9/2002 7:50:10 PM

This is the line of code that is blowing up, specifically &#34;WHERE EMP_ACTIVE &&#34;

EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;



------------
Stan Muse at 3/22/2002 6:35:51 PM

I am converting my application from access2000 to SQL2000. One of my ASPDB pages is giving the following error when selecting a record to update in SQL.

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

The field that this is occurring on was binary in access, and is defined as bit in SQL. My take on the error is that SQL isn&#39;t looking at the correct field since it says the &#34;type equals varchar&#34;.

Here is a copy of the code:

<%
Set MyDb = Server.CreateObject(&#34;Asp.db&#34
MyDb.dbUnit = 101
MYDB.dbDBType=&#34;SQL&#34;
MYDB.dbDSN = &#34;provider=SQLOLEDB; data source=DOERSSQL1DOERSSQLTEST1; Initial Catalog = doerssql; User ID= sa&#34;
mydb.dbRSCursor=1
mydb.dbmode=&#34;BOTH&#34;
mydb.dbBoolText=&#34;Yes, No, Yes, No&#34;
Mydb.dbColor=&#34;true,lightblue,true,gold,,,,true &#34;
mydb.dbGridTableTag = &#34;Border=2&#34;
mydb.dbNavigation = &#34;both&#34;
LocalFacilityID = session(&#34;facilityid&#34
mydb.dbNavigationitem= &#34;Top,Prev,Next,Bottom, update, gridrow, filter&#34;
mydb.dbOptions = &#34;GridInitSortDESC =True&#34;
mydb.dbEditParams = &#34;tablename=COSTCENT, InputSize=80, BookMarkFlds=0, tabletag=border=1, RecordScope=single, size=5x60&#34;

STRSQL = &#34;SELECT COS_PK, COS_CostCodeNumber, COS_CostCodeDescription, COS_Active, COS_SupervisorEMPFK, COS_SupervisorEmailAddress,&#34;
STRSQL = STRSQL & &#34; COS_FacilityFACFK FROM CostCent&#34;
strSQL = strSQL & &#34; WHERE Cos_FACILITYFACFK = &#34; & LocalFacilityID
mydb.dbSQL = strSQL

MYDB.dbDisplayLookUpList = &#34;(;|)Cos_SupervisorEMPFK ||Select EMP_PK, EMP_SocialSecurityNumber From Employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID
mydb.dbNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
mydb.dbFormNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
MYDB.dbEditFlds = &#34;1,2,3,4,5,6&#34;
EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;
MYDB.dbEditDropFlds = EDF
MYDB.dbFilterFlds = &#34;1&#34;
MYDB.dbUserLocalText=zDrop & &#34;,none;&#34;
MYDB.dbGridHideFlds = &#34;0&#34;
MYDB.dbEditUpdateROFlds = &#34;,1,2,3,6&#34;
MYDB.dbSelectFROM = TRUE
MYDB.dbGridInc = 20
mydb.dbNavigationIcon = &#34;Std&#34;
mydb.dbImageDir = &#34;images/images/&#34;
MyDb.aspDB &#39;Display the data!
JUSTREAD = MYDB.dbRecordCount
%>

[Stan]

Hi John,
The real problem is with the part of the statement &#34;& &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME &#34; where I retrieve the name values for display as a string, with the SSN. It appears that ASPDB parses this in a way that msSQL2000 doesn&#39;t like but which works in Access2000.

The error happens when I click on the update button. The code is trying to build a drop down list, pulling in the last name and first name to be displayed with the SSN. If I take those pieces out so that only the SSN is displayed then the update function takes me into the edit mode without an error.

I tried all the debug modes before I discovered this, I&#39;ll compare the results with and without this statement and see if it tells me anything. My local SQL guru says that this syntax should work unless aspdb needs something special for SQL.




------------
John at 4/11/2002 12:30:57 AM

Hi Stan,

I wonder what it&#39;s executing when you try and edit. If you set x.dbDebug=101 it will show the SQL that ASPdb is executing. That might give you a good clue as to what the problem is.

Also, is it a problem when you go &#34;into&#34; edit mode, or when you click the submit button to make the change. Finally, is it in Add and Update or just one of those?

Don&#39;t forget to simply the program down to 10 lines or less to show the problem.

Thanks,
John



------------
Stan at 4/10/2002 11:43:19 AM

Hi John,
Well now I&#39;m really confused, I removed the suspect statement &#34;WHERE EMP_ACTIVE&#34; completely and still get this error:

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

I can&#39;t see where my code is invoking an operator or specifying data type. The code will bring data into the grid without error, it is when I try to edit that the error occurs.Maybe the question I should be asking is how can i make sure the operator matches the data type?


------------
John at 4/10/2002 4:11:22 AM


Hi Stan,

Can you change that &#34;WHERE EMP_ACTIVE&#34; to &#34;WHERE EMP_ACTIVE = 1&#34; (or TRUE or something?)

I&#39;d reduce that down to that one WHERE EMP_ACTIVE condition and get it working and then build back up from there. I think that could be where your problem is.

John

------------
Stan at 4/9/2002 7:50:10 PM

This is the line of code that is blowing up, specifically &#34;WHERE EMP_ACTIVE &&#34;

EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;



------------
Stan Muse at 3/22/2002 6:35:51 PM

I am converting my application from access2000 to SQL2000. One of my ASPDB pages is giving the following error when selecting a record to update in SQL.

Error # (Get_RefRS - DropSQL - 2) = 80040E14(-2147217900)
Description = Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 42000
Native Error = 403

The field that this is occurring on was binary in access, and is defined as bit in SQL. My take on the error is that SQL isn&#39;t looking at the correct field since it says the &#34;type equals varchar&#34;.

Here is a copy of the code:

<%
Set MyDb = Server.CreateObject(&#34;Asp.db&#34
MyDb.dbUnit = 101
MYDB.dbDBType=&#34;SQL&#34;
MYDB.dbDSN = &#34;provider=SQLOLEDB; data source=DOERSSQL1DOERSSQLTEST1; Initial Catalog = doerssql; User ID= sa&#34;
mydb.dbRSCursor=1
mydb.dbmode=&#34;BOTH&#34;
mydb.dbBoolText=&#34;Yes, No, Yes, No&#34;
Mydb.dbColor=&#34;true,lightblue,true,gold,,,,true &#34;
mydb.dbGridTableTag = &#34;Border=2&#34;
mydb.dbNavigation = &#34;both&#34;
LocalFacilityID = session(&#34;facilityid&#34
mydb.dbNavigationitem= &#34;Top,Prev,Next,Bottom, update, gridrow, filter&#34;
mydb.dbOptions = &#34;GridInitSortDESC =True&#34;
mydb.dbEditParams = &#34;tablename=COSTCENT, InputSize=80, BookMarkFlds=0, tabletag=border=1, RecordScope=single, size=5x60&#34;

STRSQL = &#34;SELECT COS_PK, COS_CostCodeNumber, COS_CostCodeDescription, COS_Active, COS_SupervisorEMPFK, COS_SupervisorEmailAddress,&#34;
STRSQL = STRSQL & &#34; COS_FacilityFACFK FROM CostCent&#34;
strSQL = strSQL & &#34; WHERE Cos_FACILITYFACFK = &#34; & LocalFacilityID
mydb.dbSQL = strSQL

MYDB.dbDisplayLookUpList = &#34;(;|)Cos_SupervisorEMPFK ||Select EMP_PK, EMP_SocialSecurityNumber From Employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID
mydb.dbNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
mydb.dbFormNameMap=&#34;COS_PK,PRIMARY KEY; COS_COSTCODENUMBER,Cost Code #; COS_COSTCODEDESCRIPTION,Cost Code Description;COS_Active,Active;COS_SUPERVISOREMPFK, Supervisor&#39;s SSN;COS_SUPERVISOREMAILADDRESS, Supervisor&#39;s Email Address;COS_FACILITYFACFK,Facility ID&#34;
MYDB.dbEditFlds = &#34;1,2,3,4,5,6&#34;
EDF = &#34;(;|)COS_SUPERVISOREMPFK ||||select emp_pk, emp_socialsecuritynumber & &#39;, &#39; & EMP_LASTNAME & &#39;, &#39; & EMP_FIRSTNAME from employee WHERE EMP_ACTIVE & EMP_FACILITYFACFK = &#34; & LOCALFACILITYID & &#34; order by EMP_LASTNAME&#34;
MYDB.dbEditDropFlds = EDF
MYDB.dbFilterFlds = &#34;1&#34;
MYDB.dbUserLocalText=zDrop & &#34;,none;&#34;
MYDB.dbGridHideFlds = &#34;0&#34;
MYDB.dbEditUpdateROFlds = &#34;,1,2,3,6&#34;
MYDB.dbSelectFROM = TRUE
MYDB.dbGridInc = 20
mydb.dbNavigationIcon = &#34;Std&#34;
mydb.dbImageDir = &#34;images/images/&#34;
MyDb.aspDB &#39;Display the data!
JUSTREAD = MYDB.dbRecordCount
%>