ADO command causes recordset field to return null?

[downwitch]

I have the strangest error, and wonder if anyone else has seen it. I'm
using ADODB to return a set of SQL Server 2005 records via ODBC. If I
open the recordset this way

With cmd
.CommandType = adCmdStoredProc
.CommandText = "dbo.spMyProc"
Set prm = .CreateParameter("@ID", adInteger,
Value:=plngID)
.Parameters.Append prm
Set prm = .CreateParameter("@A", adBoolean, Value:=pbleA)
.Parameters.Append prm
Set prm = .CreateParameter("@B", adBoolean, Value:=pbleB)
.Parameters.Append prm

.ActiveConnection = mcnn
Set rst = .Execute
'same problem with rst.Open cmd
End With

a date/time field in the resultset is null, or as nullish as can be
determined. If I run the same thing this way

With rst
.Source = "EXEC dbo.spMyProc " & plngID & ", " &
IIf(pbleA, "1", "0") & ", " & IIf(pbleB, "1", "0")
.CursorType = adOpenStatic

.ActiveConnection = mcnn
.Open
End With

the date field is populated properly.

Has anyone else seen a bug like this? I've never seen it before--I use
setups like the first one all the time--and I can't find anything
about it on the internet(s) either.

[Rich P]

If you have the abitlity to run stored procedures on Sql Server then you
have the ability to connect via OLEDB. Don't use ODBC -- that is
probably where your problems is originating. Here is an OLEDB
connection sample

cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=YourDB;Trusted_Connecti on=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[downwitch]

I should probably convince my client to switch to OLEDB. Using DSNs
allows for rapid change of environment during dev, is the only reason
I can think of that the connection is still made via ODBC, though it's
my understanding that using ADO actually makes the point moot.

The magic parameter, actually, is the CursorLocation--as soon as I
specify adUseClient in the command, it works properly, using ODBC. I
didn't know how where to do this in a command, but it's also my
understanding that this can be costly when working with large
datasets, and is generally to be avoided. Plus, switching to the
client should hardly turn a null value back into a date. I don't see
the source of the error.

On Aug 27, 3:25 pm, Rich P <[e-mail]:

If you have the abitlity to run stored procedures on Sql Server then you
have the ability to connect via OLEDB. Don't use ODBC -- that is
probably where your problems is originating. Here is an OLEDB
connection sample

cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=YourDB;Trusted_Connecti on=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

[Rich P]

If performing an RDBMS operation correctly reduces the performance of
your system -- you need to re-evaluate the tools you are using. Access
was revolutionary in its early days because it was one of the first (if
not the first) RDBMS on the market -- with the added feature of an
integrated development environment within the RDBMS. But it has long
since been supersceded by improved technology for handling large
datasets, multi-user environments, web environments, multi-tiered
systems (which would mostly be enterprise systems).

With the newer technologies and newer times, Enterprise systems
want/need MORE capabilities than the Access model can deliver
(effectively). It turns out that the integrated Development environment
within the RDBMS (Access) was not as effective for large data/user
operations than a separate RDBMS/Development system (thus the .Net/sql
server paradigm was born). The .Net environment has significantly
overcome a lot of the obstacles/short commings of the Access model that
enterprise systems have encountered as they demand MORE of everything.

For single user single workstation operations, Access would be more
efficient that an enterprise system (less overkill). But if the word
multi-user/Network/Web comes into the picture you really should be
thinking .Net with Sql Server.

It seems like alot of developers who have been working with Access for
10 or more years have been resisting the migration to .Net. Over at my
place, I am the only person with 10+ years of RDBMS experience and the
only person who even touches Access. All the younger guys here (mostly
right out of college) are using .Net/Ruby/...Pearl seems to be making a
comeback. But even I can't keep up with these younger guys. I made it
to .Net which is at least good for enterprise stuff. So, what I would
do in your scenario is to re-evaluate the needs of your company.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[downwitch]

Sorry, I don't really follow what you're saying. I see virtually no
performance difference between SQLOLEDB and ODBC, nor was that causing
my problem; I mentioned in passing that it would be worth getting my
client to switch over, but it's hardly a priority, or even a
necessity.

My only question, at the moment, is over CursorLocation, and Access
works just fine with a server-side cursor almost all the time--in
fact, this is the first time I've seen an error in data that I could
trace to using a server-side cursor. I'm asking *how* using a client-
side cursor can somehow rectify whatever ADO bugginess might cause a
recordset to come down with an inexplicably empty column.

As for .NET, I'm perfectly at home in it, but I'm also the rare young
guy who still knows Access, and I just try to save clients money by
modeling their apps in Access until they know what they want. As has
been stated in this newsgroup repeatedly, and as I see in my work
every day and even more prominently in the work of my .NET-exclusive
colleagues, .NET is still much slower to build in. Even without using
bound forms etc.
On Aug 27, 5:29 pm, Rich P <[e-mail]:

If performing an RDBMS operation correctly reduces the performance of
your system -- you need to re-evaluate the tools you are using. Access
was revolutionary in its early days because it was one of the first (if
not the first) RDBMS on the market -- with the added feature of an
integrated development environment within the RDBMS. But it has long
since been supersceded by improved technology for handling large
datasets, multi-user environments, web environments, multi-tiered
systems (which would mostly be enterprise systems).

With the newer technologies and newer times, Enterprise systems
want/need MORE capabilities than the Access model can deliver
(effectively). It turns out that the integrated Development environment
within the RDBMS (Access) was not as effective for large data/user
operations than a separate RDBMS/Development system (thus the .Net/sql
server paradigm was born). The .Net environment has significantly
overcome a lot of the obstacles/short commings of the Access model that
enterprise systems have encountered as they demand MORE of everything.

For single user single workstation operations, Access would be more
efficient that an enterprise system (less overkill). But if the word
multi-user/Network/Web comes into the picture you really should be
thinking .Net with Sql Server.

It seems like alot of developers who have been working with Access for
10 or more years have been resisting the migration to .Net. Over at my
place, I am the only person with 10+ years of RDBMS experience and the
only person who even touches Access. All the younger guys here (mostly
right out of college) are using .Net/Ruby/...Pearl seems to be making a
comeback. But even I can't keep up with these younger guys. I made it
to .Net which is at least good for enterprise stuff. So, what I would
do in your scenario is to re-evaluate the needs of your company.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

[Rich P]

>>
As for .NET, I'm perfectly at home in it, but I'm also the rare young
guy who still knows Access, and I just try to save clients money by
modeling their apps in Access until they know what they want. As has
been stated in this newsgroup repeatedly, and as I see in my work every
day and even more prominently in the work of my .NET-exclusive
colleagues, .NET is still much slower to build in. Even without using
bound forms etc.
<<

Sorry bout the drama. But as you say - when the client knows what the
client wants. Where you sort of lose me is that if you can connect to
sql server with ODBC and run SPs, then can't you connect with OLEDB? Or
is it a permission thing? You should be able to write a connection
string like my sample and run it.

Try a basic select count(*) from some table statement

Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
...
cmd.CommandText = "Select count(*) from someTbl"
Set RS = cmd.Execute
Debug.Print RS(0)

IF this doesn't work, then it is a permission thing. But it should
work. If it does work, then run that by your client. ODBC for Access
is quite old technology with low bandwidth and limited capabilities
compared to OLEDB.
Rich

*** Sent via Developersdex http://www.developersdex.com ***