[RESOLVED] Error Type Mismatch Code 800A000D when trying to compair ADODB Recordset = 0

[joefonseca79]

I have the following VB code that I'm trying to create a text file if the results from the SQL query is greater than zero.

Here's a screenshot of my error i'm getting.



The error is on Line 31 Char 1

_________________________
***** Code below *****
_________________________


'***** Used to test if there is data for sales assignment, it creates a file with the count of records.
'***** Dated 07/13/09


set fso=WScript.CreateObject("Scripting.FileSystemObject")
vDirectoryBase=mid(WScript.ScriptFullName,1,len(WScript.ScriptFullName)-len(WScript.ScriptName))

Dim fso, f1


vRunDate=formatdatetime(now,vbShortDate)

Set Conn=WScript.CreateObject("ADODB.Connection")
conn.ConnectionString="dsn=QBCUDB;Connect Timeout=0;Command Timeout=0"
conn.Open
conn.CommandTimeout=999999

IF err.number<>0 THEN vError="Connection cannot be established" else

set RsDet=WScript.CreateObject("ADODB.RecordSet")
set Rs=WScript.CreateObject("ADODB.RecordSet")



' ****************** Summary 1 - test files

RsDet.Open "SELECT COUNT( SAPDW.DW_BILLTO.BILLTO_NAME ) FROM SAPDW.DW_CALENDAR, SAPDW.DW_BILLTO, SAPDW.DW_COMPANY, SAPDW.DW_LINE_TYPE, SAPDW.DW_SALES, SAPDW.DW_CURRENCY_RATE CURRENCY_RATE_BUS_DAYS WHERE ( SAPDW.DW_SALES.LINE_TYPE_SK=SAPDW.DW_LINE_TYPE.LINE_TYPE_SK ) AND ( SAPDW.DW_CALENDAR.DATE_KEY=SAPDW.DW_SALES.GL_DATE_SK ) AND ( SAPDW.DW_SALES.BILLTO_SK=SAPDW.DW_BILLTO.BILLTO_SK ) AND ( SAPDW.DW_BILLTO.BILLTO_RECORD = 'Y' ) AND ( SAPDW.DW_COMPANY.COMPANY_SK=SAPDW.DW_SALES.COMPANY_SK ) AND ( CURRENCY_RATE_BUS_DAYS.CURRENCY_TYPE='EURXYTD' AND SAPDW.DW_SALES.COMPANY_CURRENCY=CURRENCY_RATE_BUS_DAYS.CURRENCY_FROM ) AND ( SAPDW.DW_CALENDAR.SALES_CUR_YEAR=CURRENCY_RATE_BUS_DAYS.FISCAL_YR ) AND ( SAPDW.DW_CALENDAR.SALES_CUR_MONTH=CURRENCY_RATE_BUS_DAYS.MONTH_NBR ) AND ( SAPDW.DW_BILLTO.BILLTO_NBR <> 0 AND SAPDW.DW_BILLTO.SUB_CHANNEL_CODE In ( '10','20','30' ) AND SAPDW.DW_BILLTO.CUSTOMER_GROUP5_CODE = ' ' AND SAPDW.DW_COMPANY.COMPANY_CODE = 'FOIN' AND SAPDW.DW_LINE_TYPE.LINE_TYPE_CODE In ( 'GR','ZR','DR','CD','AR' ) )", conn
'--'OPTR'

IF RsDet = 0 THEN

'***** Create file *****

Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.CreateTextFile("C:\Documents and Settings\jfonseca\My Documents\Business Objects\EVENTS\FOIN.txt", True)

END IF
'rs.close
rsDet.close


'END IF

[si_the_geek]

Welcome to VBForums

The problem is that a Recordset is not a simple data type which just has a single value, it is an Object variable (as shown by the use of Set) and therefore has multiple values (or Properties) and methods (such as .Open).

In order to get the value of a field from a recordset (even if there is only one), you need to specify the field. There are various ways to do that, but in this case I would recommend specifying by ordinal position (0 is the first field, 1 is the second, etc), eg:

IF RsDet.Fields(0).Value = 0 THEN

In other cases it is better to use the field name (but not apt here, as you aren't using a field directly), eg:

IF RsDet.Fields("Fieldname").Value = 0 THEN

[joefonseca79]

Thanks bud it worked. I'm a VB virgin this is my 1st app vb script so i can automate something for our Business Objects environment.

[si_the_geek]

I'm happy to help.


As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).