wanted: select query with has no result but with columns in record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jan - 23 Aug 2005 08:07 GMT

Hi,

I am querying a sql server db with ado.
Sometimes I receive a record in the recordset and sometimes the recordset is
empty. This is correct and as expected.

But what I want is when I receive no records I still want to retrieve the
format (read columns) of the recordset ( the same as I would receive if there
was a record)...
So what I want is to receive a recordset with the columns defined but with
no row (or even better: an empty row)

Cheers,
Jan

Reply to this Message

Max Kudrenko - 23 Aug 2005 12:10 GMT

Jan,

Try examining the Fields collection:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdco
lfields.asp

Getting the field names can be achieved by something similar to this
code:

For Each fld In rst.Fields
Debug.Print fld.Name
Next

Hope this helps,

Max Kudrenko
Brainbench MVP Program for Visual Basic
www.brainbench.com

> Hi,
> I am querying a sql server db with ado.
[quoted text clipped - 7 lines]
> Cheers,
> Jan

Reply to this Message

Jan - 23 Aug 2005 14:45 GMT

Max,

Thanks for your post!

But in my test I executed a select query which has no results.
Of course there are no 'lines' in my recordset.
And also the fields collection stays empty...or was it filled and my test
was incorrect...?

Jan

> Jan,
>
[quoted text clipped - 25 lines]
> > Cheers,
> > Jan

Reply to this Message

Max Kudrenko - 23 Aug 2005 15:55 GMT

Jan,

As you can see here
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdob
jfield.asp),
"All of the metadata properties (Name, Type, DefinedSize, Precision,
and NumericScale) are available before opening the Field object's
Recordset." So, at least in theory, you should be able to get the
metadata even for empy recordsets. In practice, it depends on the
provider. It definitely works for MS Access. Also, the way you
construct your query might have something to do with it. If it's
relying on a stored procedure, this metadata can be difficult to
obtain. But if you dynamically construct your query in VB code, I think
this should be available.

Hope this helps,

Max Kudrenko
Brainbench MVP Program for Visual Basic
www.brainbench.com

> Max,
> Thanks for your post!
[quoted text clipped - 28 lines]
> > > Cheers,
> > > Jan

Reply to this Message

Ch. Marx - 24 Aug 2005 12:30 GMT

Maybe this is what your are looking for.

rs.open ...
if rs.eof then
rs.addnew
bnewrs = true
end if

do domthing with rs

if bnewrs then rs.cancelupdate
rs.close

Chris

> Hi,
>
[quoted text clipped - 12 lines]
> Cheers,
> Jan

Reply to this Message

Mark J. McGinty - 29 Aug 2005 19:29 GMT

> Hi,
>
[quoted text clipped - 9 lines]
> So what I want is to receive a recordset with the columns defined but with
> no row (or even better: an empty row)

When you attempt to open a forward only read only recordset (aka, firehose)
and zero records are returned by your query, you get a closed recordset.
(Closed recordsets have no fields collection.) Any other type will return
an open recordset with zero records -- you'll have to add the blank row
yourself.

Firehose is the default for recordset.Open, and is always returned by
connection.Execute (assuming the executed statement returns records.) So
you'll need to use recordset.Open, with explicitly specified CursorType and
LockType properties. (adOpenStatic and adLockOptimistic should work for you
here.)

-Mark

> Cheers,
> Jan

Reply to this Message

VB Forum / Database Access / August 2005

wanted: select query with has no result but with columns in record