Recordset is complete, but has no values

[Jordan]

I'm working with a NEON Systems ODBC driver to a legacy database and the
connection is working fine. The problem is that I'm trying to evaluate the
existence of a recordset but the actual recordset returned has a peculiarity
that I've never seen before...and it's kinda hard to explain.

When a recordset exists (the program I'm calling puts info in all 16
fields), all 16 fields come back with information in each field. Important
to note is that each field has an actual validate-able value. Meaning,
Rs.Fields(1) = "F

The odd problem is, when the recordset is returned (the program I'm calling
only puts one value into one field - meaning this record doesn't really
exist), only 1 field comes back with information. The important thing to
note here is that I CANNOT validate the fields that don't have values! I
can't test for Nulls, emply strings, field existence, field type, etc.

What I should be able to do is:

if IsNull(Rs.Fields(0)) then
'there no recordset
else
'there is record
end

....but this doesn't work. The RS object is actually the same regardless if
all fields have info or not. If I loop thru all 16 fields and write the
information, the full recordset writes one line, the non-recordset writes
one line. The only solution I can see at this point is to do this looping
and build a string. Then, if this string is so long (like a length of 35 or
more) then that would mean we have a full record, as the single field
returned in the non-recordset is lenght 3.

Clear as mud right?

Any ideas/advice would be much much appreciated.

j

[Bob Barrows]

Jordan wrote:

> I'm working with a NEON Systems ODBC driver to a legacy database and
> the connection is working fine. The problem is that I'm trying to
> evaluate the existence of a recordset but the actual recordset
> returned has a peculiarity that I've never seen before...and it's
> kinda hard to explain.
>
> When a recordset exists (the program I'm calling puts info in all 16
> fields), all 16 fields come back with information in each field.
> Important to note is that each field has an actual validate-able
> value. Meaning, Rs.Fields(1) = "F
>
> The odd problem is, when the recordset is returned (the program I'm
> calling only puts one value into one field - meaning this record
> doesn't really exist), only 1 field comes back with information. The
> important thing to note here is that I CANNOT validate the fields
> that don't have values! I can't test for Nulls, emply strings, field
> existence, field type, etc.
>
> What I should be able to do is:
>
> if IsNull(Rs.Fields(0)) then
> 'there no recordset
> else
> 'there is record
> end
>
> ...but this doesn't work. The RS object is actually the same
> regardless if all fields have info or not. If I loop thru all 16
> fields and write the information, the full recordset writes one line,
> the non-recordset writes one line. The only solution I can see at
> this point is to do this looping and build a string. Then, if this
> string is so long (like a length of 35 or more) then that would mean
> we have a full record, as the single field returned in the
> non-recordset is lenght 3.
>
> Clear as mud right?
>
> Any ideas/advice would be much much appreciated.
>
> j

Immediately after being opened, if a recordset contains no records, its EOF
property will be True. So

If rs.EOF then
'no records
else
'records exits
end if

The ADO documentation can be found at msdn.microsoft.com/library - expand
the Data Access node in the table of contents (TOC)

HTH.
Bob Barrows
--
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.

[Mark Schupp]

if I understand you there are 2 circumstances:

1. a row exists in the database that contains data in each of 16 columns.
every thing is fine for this case. when this row is selected the recordset
field collection contains 16 fields.

2. a row exists in the database that contains data in only 1 of 16 columns.
when this row is selection the recordset field collection contains only 1
field.

Is that correct?

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Jordan" <jfritts@learn.colostate.edu> wrote in message
news:err1wpE3DHA.1428@TK2MSFTNGP12.phx.gbl...

> I'm working with a NEON Systems ODBC driver to a legacy database and the
> connection is working fine. The problem is that I'm trying to evaluate the
> existence of a recordset but the actual recordset returned has a

peculiarity

> that I've never seen before...and it's kinda hard to explain.
>
> When a recordset exists (the program I'm calling puts info in all 16
> fields), all 16 fields come back with information in each field. Important
> to note is that each field has an actual validate-able value. Meaning,
> Rs.Fields(1) = "F
>
> The odd problem is, when the recordset is returned (the program I'm

calling

> only puts one value into one field - meaning this record doesn't really
> exist), only 1 field comes back with information. The important thing to
> note here is that I CANNOT validate the fields that don't have values! I
> can't test for Nulls, emply strings, field existence, field type, etc.
>
> What I should be able to do is:
>
> if IsNull(Rs.Fields(0)) then
> 'there no recordset
> else
> 'there is record
> end
>
> ...but this doesn't work. The RS object is actually the same regardless if
> all fields have info or not. If I loop thru all 16 fields and write the
> information, the full recordset writes one line, the non-recordset writes
> one line. The only solution I can see at this point is to do this looping
> and build a string. Then, if this string is so long (like a length of 35

or

> more) then that would mean we have a full record, as the single field
> returned in the non-recordset is lenght 3.
>
> Clear as mud right?
>
> Any ideas/advice would be much much appreciated.
>
> j
>
>
>

[Jordan]

> 1. a row exists in the database that contains data in each of 16 columns.

> every thing is fine for this case. when this row is selected the recordset
> field collection contains 16 fields.

True.

> 2. a row exists in the database that contains data in only 1 of 16

columns.

> when this row is selection the recordset field collection contains only 1
> field.

Not true. 1 field has data, but all other fields exist. Meaning, I can take
the value from the 1 field, and do whatever with it as it is a valid field.
The other 15 fields exist, but do not have any value that I can do anything
with. Examples:

Good Field: Resp.Write (Rs.Fields(3) ) = "000"
Bad Fields: Resp.Write (Rs.Fields(13)) *Throws an exception error

Good Field: Resp. WRite (Rs.Fields(3).Type) = "CHAR"
Bad Fields: Resp.Write (Rs.Fields(13).Type) = "DATE"

So, the actual recordset structure is complete, but if I try to test for,
retrieve or do anything with the fields that are bad, it throws an error -
IsNull doesn't work for example. (My suggestion in my first post was to loop
and build a string - won't work, throws an error).

My only solution I think what will work is to see if an error occurs - if
one does, then it's an invalid record (only the 1 field was returned),
otherwise it's a complete record.

This is really odd behavior.


"Mark Schupp" <mschupp@ielearning.com> wrote in message
news:e2hpZAF3DHA.2224@TK2MSFTNGP10.phx.gbl...

> if I understand you there are 2 circumstances:
>
>
>
> 2. a row exists in the database that contains data in only 1 of 16

columns.

> when this row is selection the recordset field collection contains only 1
> field.
>
> Is that correct?
>
> --
> Mark Schupp
> Head of Development
> Integrity eLearning
> [url]www.ielearning.com[/url]
>
>
> "Jordan" <jfritts@learn.colostate.edu> wrote in message
> news:err1wpE3DHA.1428@TK2MSFTNGP12.phx.gbl...

> > I'm working with a NEON Systems ODBC driver to a legacy database and the
> > connection is working fine. The problem is that I'm trying to evaluate

the

> > existence of a recordset but the actual recordset returned has a

> peculiarity

> > that I've never seen before...and it's kinda hard to explain.
> >
> > When a recordset exists (the program I'm calling puts info in all 16
> > fields), all 16 fields come back with information in each field.

Important

> > to note is that each field has an actual validate-able value. Meaning,
> > Rs.Fields(1) = "F
> >
> > The odd problem is, when the recordset is returned (the program I'm

> calling

> > only puts one value into one field - meaning this record doesn't really
> > exist), only 1 field comes back with information. The important thing to
> > note here is that I CANNOT validate the fields that don't have values!

I

> > can't test for Nulls, emply strings, field existence, field type, etc.
> >
> > What I should be able to do is:
> >
> > if IsNull(Rs.Fields(0)) then
> > 'there no recordset
> > else
> > 'there is record
> > end
> >
> > ...but this doesn't work. The RS object is actually the same regardless

if

> > all fields have info or not. If I loop thru all 16 fields and write the
> > information, the full recordset writes one line, the non-recordset

writes

> > one line. The only solution I can see at this point is to do this

looping

> > and build a string. Then, if this string is so long (like a length of 35

> or

> > more) then that would mean we have a full record, as the single field
> > returned in the non-recordset is lenght 3.
> >
> > Clear as mud right?
> >
> > Any ideas/advice would be much much appreciated.
> >
> > j
> >
> >
> >

>
>

[Jordan]

It's not EOF or BOF or anything. It's a valid recordset in both cases, but
only 1 field has a value in the resultset, the other has all 16 fields.


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ePdyQ9E3DHA.1052@TK2MSFTNGP12.phx.gbl...

> Jordan wrote:

> > I'm working with a NEON Systems ODBC driver to a legacy database and
> > the connection is working fine. The problem is that I'm trying to
> > evaluate the existence of a recordset but the actual recordset
> > returned has a peculiarity that I've never seen before...and it's
> > kinda hard to explain.
> >
> > When a recordset exists (the program I'm calling puts info in all 16
> > fields), all 16 fields come back with information in each field.
> > Important to note is that each field has an actual validate-able
> > value. Meaning, Rs.Fields(1) = "F
> >
> > The odd problem is, when the recordset is returned (the program I'm
> > calling only puts one value into one field - meaning this record
> > doesn't really exist), only 1 field comes back with information. The
> > important thing to note here is that I CANNOT validate the fields
> > that don't have values! I can't test for Nulls, emply strings, field
> > existence, field type, etc.
> >
> > What I should be able to do is:
> >
> > if IsNull(Rs.Fields(0)) then
> > 'there no recordset
> > else
> > 'there is record
> > end
> >
> > ...but this doesn't work. The RS object is actually the same
> > regardless if all fields have info or not. If I loop thru all 16
> > fields and write the information, the full recordset writes one line,
> > the non-recordset writes one line. The only solution I can see at
> > this point is to do this looping and build a string. Then, if this
> > string is so long (like a length of 35 or more) then that would mean
> > we have a full record, as the single field returned in the
> > non-recordset is lenght 3.
> >
> > Clear as mud right?
> >
> > Any ideas/advice would be much much appreciated.
> >
> > j

>
> Immediately after being opened, if a recordset contains no records, its

EOF

> property will be True. So
>
> If rs.EOF then
> 'no records
> else
> 'records exits
> end if
>
> The ADO documentation can be found at msdn.microsoft.com/library - expand
> the Data Access node in the table of contents (TOC)
>
> HTH.
> Bob Barrows
> --
> 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.
>
>

[Bob Barrows]

Jordan wrote:

Enough!

Show us your table structure, some sample data, and the sql statement used
to generate your recordset. Your attempts to describe the situation are very
confusing ("Good field" Bad field"??) and aren't helping at all.

I'm through guessing.

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

[Mark Schupp]

What error does it throw?

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Jordan" <jfritts@learn.colostate.edu> wrote in message
news:uow0KPG3DHA.1504@TK2MSFTNGP12.phx.gbl...

> > 1. a row exists in the database that contains data in each of 16

columns.

> > every thing is fine for this case. when this row is selected the

recordset

> > field collection contains 16 fields.

>
> True.
>

> > 2. a row exists in the database that contains data in only 1 of 16

> columns.

> > when this row is selection the recordset field collection contains only

1

> > field.

>
> Not true. 1 field has data, but all other fields exist. Meaning, I can

take

> the value from the 1 field, and do whatever with it as it is a valid

field.

> The other 15 fields exist, but do not have any value that I can do

anything

> with. Examples:
>
> Good Field: Resp.Write (Rs.Fields(3) ) = "000"
> Bad Fields: Resp.Write (Rs.Fields(13)) *Throws an exception error
>
> Good Field: Resp. WRite (Rs.Fields(3).Type) = "CHAR"
> Bad Fields: Resp.Write (Rs.Fields(13).Type) = "DATE"
>
> So, the actual recordset structure is complete, but if I try to test for,
> retrieve or do anything with the fields that are bad, it throws an error -
> IsNull doesn't work for example. (My suggestion in my first post was to

loop

> and build a string - won't work, throws an error).
>
> My only solution I think what will work is to see if an error occurs - if
> one does, then it's an invalid record (only the 1 field was returned),
> otherwise it's a complete record.
>
> This is really odd behavior.
>
>
> "Mark Schupp" <mschupp@ielearning.com> wrote in message
> news:e2hpZAF3DHA.2224@TK2MSFTNGP10.phx.gbl...

> > if I understand you there are 2 circumstances:
> >
> >
> >
> > 2. a row exists in the database that contains data in only 1 of 16

> columns.

> > when this row is selection the recordset field collection contains only

1

> > field.
> >
> > Is that correct?
> >
> > --
> > Mark Schupp
> > Head of Development
> > Integrity eLearning
> > [url]www.ielearning.com[/url]
> >
> >
> > "Jordan" <jfritts@learn.colostate.edu> wrote in message
> > news:err1wpE3DHA.1428@TK2MSFTNGP12.phx.gbl...

> > > I'm working with a NEON Systems ODBC driver to a legacy database and

the

> > > connection is working fine. The problem is that I'm trying to evaluate

> the

> > > existence of a recordset but the actual recordset returned has a

> > peculiarity

> > > that I've never seen before...and it's kinda hard to explain.
> > >
> > > When a recordset exists (the program I'm calling puts info in all 16
> > > fields), all 16 fields come back with information in each field.

> Important

> > > to note is that each field has an actual validate-able value. Meaning,
> > > Rs.Fields(1) = "F
> > >
> > > The odd problem is, when the recordset is returned (the program I'm

> > calling

> > > only puts one value into one field - meaning this record doesn't

really

> > > exist), only 1 field comes back with information. The important thing

to

> > > note here is that I CANNOT validate the fields that don't have values!

> I

> > > can't test for Nulls, emply strings, field existence, field type, etc.
> > >
> > > What I should be able to do is:
> > >
> > > if IsNull(Rs.Fields(0)) then
> > > 'there no recordset
> > > else
> > > 'there is record
> > > end
> > >
> > > ...but this doesn't work. The RS object is actually the same

regardless

> if

> > > all fields have info or not. If I loop thru all 16 fields and write

the

> > > information, the full recordset writes one line, the non-recordset

> writes

> > > one line. The only solution I can see at this point is to do this

> looping

> > > and build a string. Then, if this string is so long (like a length of

35

> > or

> > > more) then that would mean we have a full record, as the single field
> > > returned in the non-recordset is lenght 3.
> > >
> > > Clear as mud right?
> > >
> > > Any ideas/advice would be much much appreciated.
> > >
> > > j
> > >
> > >
> > >

> >
> >

>
>

[Jordan]

I've been guessing ever since I encountered this issue...

SQL Statement:

Set Rs = Conn.Execute("CALL SISGP03('input var')"

This is a legacy system that executes programs (SISGP03 in this case) to
return an output table. The only statement in this program (which I have no
control over) that causes the problem is that the value "000" is inserted
into the output table to ensure at least one record is returned, which is
the recordset with 1 field and 15 empty.

Sample Code/Data:

I've done the exact same thing w/ a recordset too, but below is how I've
left it, in an array, cause neither works (advanced code I know :)

Do Until n = UBound(arrHoldCodes,2) + 1
Response.Write (arrHoldCodes(0,n) & " - 1")
Response.Write (arrHoldCodes(1,n) & " - 2")
...
Response.Write (arrHoldCodes(15,n) & " - 16")
n = n + 1
Loop

For the good recordset, I get values and 16 lines of data:

ADDR - 1
000 - 2
...
Records - 16

For the bad recordset, I get 1 valuse and 1 line of data:

000 - 2 'this is a *code* field soley used as an indicator -
well, not in this case, but in
'evaluation statements inside the program, it is
updated to different codes

Any functions I try to run against a field that is not returned in the Bad
Recordset, either evaluate to their default value (IsNull forexample) or
throw an error (myVar = Rs.Fields(0)) throws the error, "Exception occured"
or "Either EOF or BOF is true..." BUT, if I do this, Rs.Fields(0).Type I'll
get "129" (i.e. CHAR) - gotta love it.

The table structure is simply all CHAR fields, with one DATE field. This
really wouldn't be a problem if the field I got back was coded better,
meaning that if it was a good record, the code could stay "000" and a bad
record could be "001." Needless to say this is not being done and I'm
stuck.

Jordan



"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uZWZdZG3DHA.540@tk2msftngp13.phx.gbl...

> Jordan wrote:
>
> Enough!
>
> Show us your table structure, some sample data, and the sql statement used
> to generate your recordset. Your attempts to describe the situation are

very

> confusing ("Good field" Bad field"??) and aren't helping at all.
>
> I'm through guessing.
>
> Bob barrows
> --
> 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.
>
>

[Jordan]

here some examples:

1. testString = CStr(Rs.Fields(12))
2. Resp.Write Rs.Fields(12)

throw:
error '80020009'
Exception occurred.



1. Response.Write (Rs.Status), and other Rs methods do this too

throws:

ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

....but of course, Resp.Write (Rs.EOF) writes "false"

And these don't throw an error but don't tell me anything about the "lack"
of value

1. Resp.Write IsNull(Rs.Fields(12)) 'writes "false"

2. Resp.Write Rs.Fields(12) 'writes nothing

3. Resp.Write Rs.Fields(12).Type 'writes "129"



argh!

j






"Mark Schupp" <mschupp@ielearning.com> wrote in message
news:e$CRpkG3DHA.3944@tk2msftngp13.phx.gbl...

> What error does it throw?
>
> --
> Mark Schupp
> Head of Development
> Integrity eLearning
> [url]www.ielearning.com[/url]
>
>
> "Jordan" <jfritts@learn.colostate.edu> wrote in message
> news:uow0KPG3DHA.1504@TK2MSFTNGP12.phx.gbl...

> > > 1. a row exists in the database that contains data in each of 16

> columns.

> > > every thing is fine for this case. when this row is selected the

> recordset

> > > field collection contains 16 fields.

> >
> > True.
> >

> > > 2. a row exists in the database that contains data in only 1 of 16

> > columns.

> > > when this row is selection the recordset field collection contains

only

> 1

> > > field.

> >
> > Not true. 1 field has data, but all other fields exist. Meaning, I can

> take

> > the value from the 1 field, and do whatever with it as it is a valid

> field.

> > The other 15 fields exist, but do not have any value that I can do

> anything

> > with. Examples:
> >
> > Good Field: Resp.Write (Rs.Fields(3) ) = "000"
> > Bad Fields: Resp.Write (Rs.Fields(13)) *Throws an exception error
> >
> > Good Field: Resp. WRite (Rs.Fields(3).Type) = "CHAR"
> > Bad Fields: Resp.Write (Rs.Fields(13).Type) = "DATE"
> >
> > So, the actual recordset structure is complete, but if I try to test

for,

> > retrieve or do anything with the fields that are bad, it throws an

error -

> > IsNull doesn't work for example. (My suggestion in my first post was to

> loop

> > and build a string - won't work, throws an error).
> >
> > My only solution I think what will work is to see if an error occurs -

if

> > one does, then it's an invalid record (only the 1 field was returned),
> > otherwise it's a complete record.
> >
> > This is really odd behavior.
> >
> >
> > "Mark Schupp" <mschupp@ielearning.com> wrote in message
> > news:e2hpZAF3DHA.2224@TK2MSFTNGP10.phx.gbl...

> > > if I understand you there are 2 circumstances:
> > >
> > >
> > >
> > > 2. a row exists in the database that contains data in only 1 of 16

> > columns.

> > > when this row is selection the recordset field collection contains

only

> 1

> > > field.
> > >
> > > Is that correct?
> > >
> > > --
> > > Mark Schupp
> > > Head of Development
> > > Integrity eLearning
> > > [url]www.ielearning.com[/url]
> > >
> > >
> > > "Jordan" <jfritts@learn.colostate.edu> wrote in message
> > > news:err1wpE3DHA.1428@TK2MSFTNGP12.phx.gbl...
> > > > I'm working with a NEON Systems ODBC driver to a legacy database and

> the

> > > > connection is working fine. The problem is that I'm trying to

evaluate

> > the

> > > > existence of a recordset but the actual recordset returned has a
> > > peculiarity
> > > > that I've never seen before...and it's kinda hard to explain.
> > > >
> > > > When a recordset exists (the program I'm calling puts info in all 16
> > > > fields), all 16 fields come back with information in each field.

> > Important

> > > > to note is that each field has an actual validate-able value.

Meaning,

> > > > Rs.Fields(1) = "F
> > > >
> > > > The odd problem is, when the recordset is returned (the program I'm
> > > calling
> > > > only puts one value into one field - meaning this record doesn't

> really

> > > > exist), only 1 field comes back with information. The important

thing

> to

> > > > note here is that I CANNOT validate the fields that don't have

values!

> > I

> > > > can't test for Nulls, emply strings, field existence, field type,

etc.

> > > >
> > > > What I should be able to do is:
> > > >
> > > > if IsNull(Rs.Fields(0)) then
> > > > 'there no recordset
> > > > else
> > > > 'there is record
> > > > end
> > > >
> > > > ...but this doesn't work. The RS object is actually the same

> regardless

> > if

> > > > all fields have info or not. If I loop thru all 16 fields and write

> the

> > > > information, the full recordset writes one line, the non-recordset

> > writes

> > > > one line. The only solution I can see at this point is to do this

> > looping

> > > > and build a string. Then, if this string is so long (like a length

of

> 35

> > > or
> > > > more) then that would mean we have a full record, as the single

field

> > > > returned in the non-recordset is lenght 3.
> > > >
> > > > Clear as mud right?
> > > >
> > > > Any ideas/advice would be much much appreciated.
> > > >
> > > > j
> > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Bob Barrows]

Jordan wrote:

> I've been guessing ever since I encountered this issue...
>
> SQL Statement:
>
> Set Rs = Conn.Execute("CALL SISGP03('input var')"
>
> This is a legacy system that executes programs (SISGP03 in this case)
> to return an output table. The only statement in this program (which
> I have no control over) that causes the problem is that the value
> "000" is inserted into the output table to ensure at least one record
> is returned, which is the recordset with 1 field and 15 empty.

Are you sure? When it has one value, what does rs.Fields.Count show you?

Bob Barrows


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

[Bob Barrows]

Jordan wrote:

> I've been guessing ever since I encountered this issue...
>
> I've done the exact same thing w/ a recordset too, but below is how
> I've left it, in an array, cause neither works (advanced code I know

Wait! This is a GetRows array? I ignored this part initially since I did not
understand where this array came from. If it's a GetRows array, then you
have your answer:
when one value is returned, it is returned in a record containing only one
field.

> :)
>
> Do Until n = UBound(arrHoldCodes,2) + 1
> Response.Write (arrHoldCodes(0,n) & " - 1")
> Response.Write (arrHoldCodes(1,n) & " - 2")
> ...
> Response.Write (arrHoldCodes(15,n) & " - 16")
> n = n + 1
> Loop
>
> For the good recordset, I get values and 16 lines of data:
>
> ADDR - 1
> 000 - 2
> ...
> Records - 16
>
> For the bad recordset, I get 1 valuse and 1 line of data:
>

A single field. So, simply test rs.Fields.Count. If it's 1, then don't try
to read the value from any of the nonexisting fields.

Bob Barrows

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

[Jordan]

I really appreciate your help, but I'm just going to piss you off by saying,

Rs.Fields.Count = 20 regardless of if it's the good or bad recordset. That's
why I can detect the type of the fields even when I can't retreive/evaluate
the field value.

....and I'll just piss you off again in the reply to your second message.

Get this too: I have SQL Server 2000 here, so I setup a linkedserver and got
the statment to execute using the OPENQUERY method and SQLSERV returned the
exact same thing, a complete record set w/ only one value. I have even tried
catching the values in variables and checking the ISNULL on them, same
thing. The values are nondeterministic.


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23m0Eq%23G3DHA.1184@TK2MSFTNGP10.phx.gbl...

> Jordan wrote:

> > I've been guessing ever since I encountered this issue...
> >
> > SQL Statement:
> >
> > Set Rs = Conn.Execute("CALL SISGP03('input var')"
> >
> > This is a legacy system that executes programs (SISGP03 in this case)
> > to return an output table. The only statement in this program (which
> > I have no control over) that causes the problem is that the value
> > "000" is inserted into the output table to ensure at least one record
> > is returned, which is the recordset with 1 field and 15 empty.

>
> Are you sure? When it has one value, what does rs.Fields.Count show you?
>
> Bob Barrows
>
>
> --
> 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.
>
>

[Jordan]

> Wait! This is a GetRows array? I ignored this part initially since I did

not

> understand where this array came from. If it's a GetRows array, then you
> have your answer:
> when one value is returned, it is returned in a record containing only one
> field.

It can be an GetRows array. I'm trying anything - going to try GetString
after
I finishing replying.

Problem is, the entire 2D array structure is created - all 16 elements are
present
in the array even though, as with the recordset, I can't do anything with
the values.

UBound(myArray) returns 15 regardless!

I can't get the array to throw an error when I'm trying to retrieve/tweak
the values, but I
also can't get anymore out of it.

[Jordan]

well, it just throws an error with the GetString method...drawing board

[Bob Barrows]

Jordan wrote:

> I really appreciate your help, but I'm just going to piss you off by
> saying,
>
> Rs.Fields.Count = 20 regardless of if it's the good or bad recordset.
> That's why I can detect the type of the fields even when I can't
> retreive/evaluate the field value.
>
> ...and I'll just piss you off again in the reply to your second
> message.
>
> Get this too: I have SQL Server 2000 here, so I setup a linkedserver
> and got the statment to execute using the OPENQUERY method and
> SQLSERV returned the exact same thing, a complete record set w/ only
> one value. I have even tried catching the values in variables and
> checking the ISNULL on them, same thing. The values are
> nondeterministic.
>

Use
rs.Save "filename",1
to save your recordset to a file and send it to me via email (remove the
NOSPAM from my email address). Actually, send me both versions. I need to
see this firsthand.

Just to be sure I understand: your problem is distinguishing between the two
cases so you don't generate an error by reading a field that contains no
data, right?

Bob Barrows

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

[Jordan]

> Just to be sure I understand: your problem is distinguishing between the
two

> cases so you don't generate an error by reading a field that contains no
> data, right?

Just RELIABLY distinguishing between the two cases is my goal. Basically, I
want to do this:

If <RecordsExist> Then
'show records and halt
Else
'no records to show, move to next step
End If

Even if I had to catch an error to get done with this I would. Granted, not
the best
solution, but at least something.

[Bob Barrows]

Jordan wrote:

>> Just to be sure I understand: your problem is distinguishing between
>> the two cases so you don't generate an error by reading a field that
>> contains no data, right?

>
> Just RELIABLY distinguishing between the two cases is my goal.
> Basically, I want to do this:
>
> If <RecordsExist> Then
> 'show records and halt
> Else
> 'no records to show, move to next step
> End If
>
> Even if I had to catch an error to get done with this I would.
> Granted, not the best
> solution, but at least something.

Well, I had no error using GetString

Well, it looks like the first two fields always contain "UP" and "FOUND",
and the 17th (STATUS_CODE) always contains .000

HOLD_TYPE is a fixed-length string field, so I tried several things,
including if rs(2) = " " until I discovered that the field contains
nonprintable characters. When I used Asc() to find out the character codes
for the characters in the field, I found it contained two characters with
code 0. So this is what I did:

if asc(left(rs(2),1)) = 0 then
Response.Write "No records were returned"
else
'etc.

HTH,
Bob Barrows

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

[Jordan]

> Well, it looks like the first two fields always contain "UP" and "FOUND",

> and the 17th (STATUS_CODE) always contains .000
>
> HOLD_TYPE is a fixed-length string field, so I tried several things,
> including if rs(2) = " " until I discovered that the field contains
> nonprintable characters. When I used Asc() to find out the character codes
> for the characters in the field, I found it contained two characters with
> code 0.

LOL... I just got there too. Thanks for all of your help. I knew that the
fields were fixed becuase an ealier condition I check is the "FOUND" value
and that I had to trim. So, I've been playin' round with the ASCII character
codes, and I couldn't figure out why nothing was matching up. Needless to
say, I've not run into a need to evaluate nonprintable characters
before...which seems odd to me, but man, stumped me for waaaaaay to long.

Thanks again Bob. Much appreciated.

j