adOpenDynamic v adOpenKeyset - Recordsets 'Bombing' out

Problem that's been bugging me

Back End - MS SQL Sever 2000
Front End - Access 2003 SP 1
MDAC 2.8 Installed
OS - Win XP Pro SP 2

'this doesn’t work - returns 0 rows from Sotred Proc
rs.Open strProc, gAcn, adOpenDynamic, adLockOptimistic

'this works, returns all rows
rs.Open strProc, gAcn, adOpenKeyset, adLockOptimistic

gAcn is my ADODB connection to MS SQL Server using a trusted connection

Some recordsets (Stored Procs) do work with adOpenDynamic but some 'bomb' out.  Had a few hours playing around and adOpenKeyset seemed to work every time

If I create a DSN and run a "Pass-through query" with the exact same query (strProc), it returns everything, every time

I am an admin on my PC with the necessary privileges to run the stored proc

I can return all the data (bind it to a datagrid) if I do the same thing with ADO .Net (using VB .Net)

I'm lost… Any insight, some SP or hot fix I'm missing, or am i just an idiot.

PS. the exact same code works on another PC running Win 2k, but not using a trusted conneciton (the username and password way)


Dave :-)
LVL 34
flavoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
Hi Dave,

what is the cursorlocation of your global connection object?

Alan
0
flavoAuthor Commented:
i used both client and server..  hmm..... or did i test server with adOpenDynamic

Im no ADO "guru" infact, i NEVER use it... Why would that cause a problem  (forgive my negligence )
0
Alan WarrenApplications DeveloperCommented:
Ive seen similar thing before, and from memory it was to do with the cursorlocation.
Not sure you can change the cursorlocation once the connection object has been opened.
I usually create a global connect string, then instantiate a new connection object using the global connection string wherever I need a connection.

  But you can try this see  if it has any affect.


  Dim objCmd  As ADODB.Command
  Dim objRs As ADODB.Recordset
 
  Set objRs = New ADODB.Recordset
  With objRs
    .CursorType = adOpenDynamic
    .CursorLocation = adUseServer
  End With
 
  Set objCmd = New ADODB.Command
 
  With objCmd
    .ActiveConnection = gAcn
    .CommandType = adCmdStoredProc
    .CommandText = strProc
    Set objRs  = .Execute
    ' note sql connections often return -1 for the recordcount no matter how many records are returned.
  End With


Alan




0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

flavoAuthor Commented:
hmm... Well im just about to head home (and face peak hour traffic)
Ill give it a crank when i get home (maybe 30mins or so)

Cheers Alan!

Dave :-)
0
flavoAuthor Commented:
hmmm just did some more tests..

If i use adUseClient it works, if i use adUseServer it bombs out...(with adOpenDynamic)

hmmm... looks like i have to do some light reading tonight..
0
Alan WarrenApplications DeveloperCommented:
Ping!

you back on deck now Dave?
0
flavoAuthor Commented:
Sure am.. stupid wireless keeps going out.. only about 10m away from the router, moved back into the lounge room.. may as well be using a cord still :-(
0
Alan WarrenApplications DeveloperCommented:
have you set up encryption yet?
0
flavoAuthor Commented:
i havent done anything, it was a big enough strugle to get it up..  really should do that hey.... maybe one of the grannies in the building could be hijacking my pc...
0
Alan WarrenApplications DeveloperCommented:
I've no doubt about it - LOL

can you browse into your router

http://192.168.1.1/   

or whatever the starting address is set to, often 192.168.1.100

Alan
0
Alan WarrenApplications DeveloperCommented:
re: this q
  are you using JET mdb/adp?
0
flavoAuthor Commented:
mdb

Just lost connection again (room mate is on cordless phone, maybe thats it??) back to being cable'ly joe
0
Alan WarrenApplications DeveloperCommented:
You can find the address of your router by using IPCONFIG at a command prompt

C:\Documents and Settings\Alan>ipconfig

Windows IP Configuration


Ethernet adapter Local Area Connection:

        Connection-specific DNS Suffix  . : internode.on.net
        IP Address. . . . . . . . . . . . : 192.168.1.102
        Subnet Mask . . . . . . . . . . . : 255.255.255.0
        Default Gateway . . . . . . . . . : 192.168.1.1                   '<--Address

Most routers have a default  page at this address, easy setup
0
flavoAuthor Commented:
There is a firewall, but not sure what i can / cant block.. my room mate didnt have to do anything to connect to the internet after i spent ages setting it up on my pc, he just browsed freely... cant seem to see each others pcs, not too fussed if the old lady down stairs gets free internet.. good luck to her...
0
flavoAuthor Commented:
yeah this is it -> 192.168.1.1  had to use it to set it up.. using IE to goto it didnt work for some reason.. ended up using start -> run, worked a treat..
0
Alan WarrenApplications DeveloperCommented:
might be set up as https://192.168.1.1
0
flavoAuthor Commented:
nope.. wouldnt i need to be connected to the internet to get a SSL for that to work??
0
Alan WarrenApplications DeveloperCommented:
Anyway Dave, I think the problem you are having with the dynamic cursor thingy, getting technical now huh, is this:

JET cannot instantiate a serverside cursor, no way, caint be done.
You can set one but it is automatically converted, I read that on msdn somewhere, wish I saved the link.
On the other hand stored procs are always executed serverside, so there seems to be some ambiguity.
ADO faithfully trying to return a serverside cursor but JET dilligently converting it.

I always use keyset, seems less problematic.

Alan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
flavoAuthor Commented:
What do you do about new records??
0
flavoAuthor Commented:
>> You can set one but it is automatically converted, I read that on msdn somewhere, wish I saved the link.

Hmmm.. so you're saying that Jet will "change" adUseServer  to adUseClient?

What's the difference anyway?
0
Alan WarrenApplications DeveloperCommented:
ping...
0
flavoAuthor Commented:
keeping me on my toes hey...
0
Alan WarrenApplications DeveloperCommented:
sorry mate, just fartin around ":0)
0
flavoAuthor Commented:
its fine.. need a good laugh to stay awake at this crazzy hour..

Have to be up for my "real" job in 6 hrs..
0
Alan WarrenApplications DeveloperCommented:
yes I have to deliver a job at  9:30 am Colorado time  - 2hrs and 20 mins from now. hmmm...
0
flavoAuthor Commented:
LOL!

And so the fun continues..

Oh well, i may as well leave you to it mate, im getting a little to tired for my liking... oh what the heck, one more XXXX
0
Alan WarrenApplications DeveloperCommented:
the q on global MDW looks like fun, not.
0
flavoAuthor Commented:
I have NEVER touched mdw or any security in Access.. seems like a waste of time to me..
0
Alan WarrenApplications DeveloperCommented:
here here!
  nothing but trouble, that and dns' and replication objects
0
flavoAuthor Commented:
replication!

Had a look, scared me, never went back.. seems like WAY to much trouble.. Plus i usually use 97, hence my 0 knowledge of ADO :-)
0
Alan WarrenApplications DeveloperCommented:
Just reading this from: http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=624&printer=t
its C++ code but the reading is good


MSDN Online Reference


CursorLocation Property
Indicates the location of the cursor service.
Settings And Return Values
Sets or returns a Long value that can be set to one of the CursorLocationEnum values.
Remarks
This property allows you to choose between various cursor libraries accessible to the provider. Usually, you can choose between using a client-side cursor library or one that is located on the server.
This property setting affects connections established only after the property has been set. Changing the CursorLocation property has no effect on existing connections.
Cursors returned by the Execute method inherit this setting. Recordset objects will automatically inherit this setting from their associated connections.
This property is read/write on a Connection or a closed Recordset, and read-only on an open Recordset.
Remote Data Service Usage When used on a client-side Recordset or Connection object, the CursorLocation property can only be set to adUseClient.

MSDN Online Reference


Uses client-side cursors supplied by a local cursor library. Local cursor services often will allow many features that driver-supplied cursors may not, so using this setting may provide an advantage with respect to features that will be enabled.
0
rockiroadsCommented:
u guys are up late

Alan, hows the contracting business
Dave, you know whereabouts your gonna be in England?

Some terrible stuff I received recently


A BLOKE and his girlfriend were making love in the back of his van when
all of a sudden she shouted at him to whip her.
"Find something to whip me with now! If you do it will be the best sex
you have ever had" she screamed.
So he opened the window, snapped off the aerial and started whipping
her
with it.
Three weeks later she went to the doctor and asked him to take a look
at
some infected cuts that she had on her back.
The doctor said: "These are really badly infected.  I bet you got them
making wild passionate love didn't you?''
"Yes" she replied, ''But how did you know that?'' she asked.
"Well, this is the worst case of van aerial disease I have ever seen'.'
---------------------------------

I SAW six men kicking and punching the mother-in-law.
My neighbour said: "Are you going to help?"
"No" I said, "six should be enough."
---------------------------------

DID you hear about the dyslexic pimp? He bought a warehouse.
---------------------------------

THREE old Ladies named Gertrude, Maude, and Tilly were sitting on a
park
bench having a quiet conversation when a flasher approached from across
the
park.
The man came up to the ladies, stood right in front of them, opened his
trench coat and exposed himself.
Gertrude immediately had a stroke.
And then Maude also had a stroke.
But Tilly, being older and feebler, bless her heart, couldn't reach
that
far.
---------------------------------
CLUBBERS in Yorkshire have taken to using dental syringes to inject
Ecstasy directly into their mouths.
This dangerous practice is known as "E by gum".
---------------------------------

A CHAP walks into a pub and orders himself a beer.
He notices that Vincent Van Gogh is sitting on the next stall, and asks
him if he wants a beer.
"No thanks," replied Vincent, "I've got one ear."
---------------------------------
WHY did the jelly baby go to school?
Because he wanted to be a smarty.
---------------------------------

A LADY walked into a drug store and told the pharmacist she needed some
cyanide. The pharmacist said: "Why in the world do you need cyanide?"
The lady then explained she needed it to poison her husband. The
pharmacist's eyes got big and he said: "Lord have mercy, I can't give
you
cyanide to kill your husband!
"That's against the law! They'll throw both of us in jail and I'll lose
my
license."
Then the lady reached into her purse and pulled out a picture of her
husband in bed with the pharmacist's wife and handed it to pharmacist.
The pharmacist looked at the picture and replied: "Well now, you didn't
tell me you had a prescription."
0
flavoAuthor Commented:
There's way to many -> for my liking in that code :-)

Im gonig to hit the hay Alan.

Good luck with your project mate!

Dave :-)
0
flavoAuthor Commented:
>> Dave, you know whereabouts your gonna be in England?

Where ever i get work
0
Alan WarrenApplications DeveloperCommented:
Love the van aerial one - ROTFLMAO
0
Alan WarrenApplications DeveloperCommented:
did you hear about the irishman with lumps all over his head?
0
flavoAuthor Commented:
Yes, you are one sick puppy Rock!

Now i really better go...

ohh.. still got beer... maybe a few more minutes..
0
Alan WarrenApplications DeveloperCommented:
He tried to hang himself with a rubber-band
0
Alan WarrenApplications DeveloperCommented:
You've inspired me dave, got a chilling VB from the fridge, mmm
0
Alan WarrenApplications DeveloperCommented:
0
flavoAuthor Commented:
LOL!

Except for that VB stuff... yuck! :P

Got a deadline in 1 1/2 hours, lets open a beer ;-) I like your style!
0
flavoAuthor Commented:
<-- accidental bad link removed -->

Alan
PageEd(Databases)
0
flavoAuthor Commented:
0
Alan WarrenApplications DeveloperCommented:
whoops! whoops?, I better edit that one for ya
0
rockiroadsCommented:
Im at work, no beer, but eh, its my anniversary today, and birthday tomorrow
So drinking on a school nite, Im allowed to have a beer tonite and tomorrow


talking about anniversary's, that reminds me of the wife

How do you turn a fox into an elephant?
Marry it!

urm, thats bad!



ok, last lot, whilst your drinking your last beer

So I said to the Gym instructor "Can you teach me to do the splits?".
He said "How flexible are you?". I said "I can't make Tuesdays".

--

Do you know I've got a friend who's fallen in love with two school bags,
he's bisatchel.

--
So I said "Do you want a game of Darts?", he said "OK then",
I said "Nearest to bull starts".

He said "Baa", I said "Moo", he said "You're closest".

--

A polar bear walks into a bar and says to the bartender: "I'll have a gin......................................................
.....

.....

.....

.....

.....

.....

.....

.....

.....

.....and tonic."

The bartender asks: "What's with the big pause?"

The bear responds: "I dunno... I've always had them."

--

What fruit teases you a lot?
A ba na..na..na..na..na!

--

and Ive saved the best for last


One day God calls down to Noah and says, "Noah me old china, I wants you to make me a new Ark"

Noah replies, "Nay probs God, me old supreme being, anything you want after all you're the boss!"

But God interrupts, "Ah but there's a catch this time Noah, I want not just a couple of decks,........ I want 20 decks one on top of the other."

"20 DECKS!" screams Noah, "Well, ok big man, what ever you say,should I fill it up with all the animals just like last time?"

"Yep, that's right, well..... sort of right..... this time I just want you to fill it up with fish" God answers.

"Fish?" Queries Noah.

"Yep, fish........ well, to make it more specific Noah, I want Carp, wall to wall, floor to ceiling Carp!"

Noah looks to the skies, "OK God baby, let me get this right, you want a new Ark?"

"Check."

"With 20 decks, one on top of the other?"

"Check."

"And you want it full of Carp?"

"Check"

"Why?" asks the perplexed Noah, who was slowly but surely getting to the end of his tether!

"Dunno" says God, "I just always fancied a Multi-Story Carp Ark!"





good nite chaps, Ive still got about 4hrs to do before I can leave this place!


0
flavoAuthor Commented:
Good laugh to end the night (now early morning)

I think i may sleep in tomorrow :-)

Later
        z
       z
     z
    z
:-|
0
Alan WarrenApplications DeveloperCommented:
CarpArk - thats terrible rock

see ya L8a mate
0
flavoAuthor Commented:
Thanks Alan.. now how did i forget the au and the extra x

Maybe i should get a job a telstra :-)
0
Alan WarrenApplications DeveloperCommented:
Yes, it happens hey?
0
flavoAuthor Commented:
Thanks Alan,

You got me in the right direction :-)
0
Alan WarrenApplications DeveloperCommented:
Hi Dave,

thanks for the points, that looks like the link I was thinking of.

Alan
0
flavoAuthor Commented:
500 points... its the least i could do mate :-)
0
rockiroadsCommented:
there is a lot of beer talk here, but how are you guys into drinking mexican stuff, in particular, tequilia

before I go into the main joke, I remember this from the Stranglers concert

what did the mexican fireman name his two sons?
hose and hose b

why did the mexican push his wife off the cliff?
tequilia!


ok, main joke


A Mexican is strolling down the street in Mexico City and kicks a bottle
lying in the street. Suddenly out of the bottle comes a Genie.  The Mexican
is stunned and the Genie says, "Hello master, I will grant you one wish,
anything you want."

The Mexican begins thinking, "Well, I really like drinking tequila."
Finally the Mexican says, "I wish to drink tequila whenever I want, so make
me pee tequila."

The Genie grants him his wish. When the Mexican gets home, he gets a glass
out of the cupboard and pees in it. He looks at the glass and it's clear.
Looks like tequila. Then he smells the liquid. Smells like tequila. So he
takes a taste and it is the best tequila he has ever tasted. The Mexican
yells to his wife,"Consuelo, Consuelo, come quickly!"

She comes running down the hall and the Mexican takes another glass out of
the cupboard  and fills it.  He tells her to drink it. It is tequila.
Consuelo is reluctant but goes ahead and takes a sip. It is the best tequila

she has ever tasted. The two drank and partied all night.

The next night the Mexican comes home from work and tells his wife to get
two glasses out of the cupboard. He proceeds to fill the two glasses.  The
result is the same. The tequila is excellent and the couple drink until the
sun comes up.

Finally Friday night comes and the Mexican comes home and tells his wife,
"Consuelo, grab one glass from the cupboard and we will drink Tequila."

His wife gets the glass from the cupboard and sets it on the table.  The
Mexican begins to fill the glass; and when he fills it, his wife asks him,
"But Pancho, why do we need only one glass?"

Pancho raises the glass and says, "Because tonight, Mi Amor, you drink from
the bottle."

Arriba!!!!
0
Alan WarrenApplications DeveloperCommented:
one tequilia, two tequilia, three tequilia, floor!
0
flavoAuthor Commented:
That's about right...
hmm. i have a bottle of tequilia at home, maybe i wont have beers tonight for a change..

Ok,  party at Dave's house

Rocki, you better get on a plane 16hours ago (sorry for the late notice)
Alan, you may still be able to make it

:-)
0
Alan WarrenApplications DeveloperCommented:
Hi Dave,
sorry mate I got a better offer tonight, 5'0" dark brown eyes, 35 years old, about 40kg.

":0)
0
flavoAuthor Commented:
35.. too old for me :-)

Have a good one mate!

Dave :-)
0
flavoAuthor Commented:
Alan,

What type of router do you use?  I have a linksys and it keeps dropping out, read all the posts on EE about, seems like its a common problem.. probably shouldnt have let my roommate buy it hey...
0
rockiroadsCommented:
5" and zero inches, sounds good, better than tequilia. Not heard the one,two,...,floor one before. must remember that one.

Dave, real late notice, perhaps next time, when England take on the Aussies for the ashes, I was hoping the kiwi's beat the aussies, they then lose confidence, which gives England a great chance of perhaps winning two matches instead of the usual one!

Ive got a linksys router, it does drop connections occasionally but not that often. Not had real problems with it. The only place it loses connection is when Im in the back room, signal strength is very low there.

0
flavoAuthor Commented:
signal strength is mostly high (only at most 10m away), hasnt dropped out tonight.. {yet}.

I guess ill just get used to it.. hey, i used dial-up for the first 18 months i lived here, this ADSL is pure GOLD!
0
rockiroadsCommented:
I have a cable modem, use Broadband from my cable provider. Big big difference!
No download limits, so Im happily downloading stuff all day every day

I wonder if it makes a difference which router you have. I have WRT54G one, made for cable modems, not ADSL
0
flavoAuthor Commented:
i have a WAG54G - ADSL & Router in one, with a Belkin 54g card sticking out the side of my laptop
0
flavoAuthor Commented:
>> No download limits

As do i, 1.5Mbit connection, fast as i could find for residential
0
rockiroadsCommented:
some of the ISP's here impose a download limit, bit naff really

your setup sounds good, Ive found a place to download dvd rips of bollywood movies, keeps the wife happy, havent found one for hollywood movies yet. File sharing stuff isnt so good with dvd rips
0
flavoAuthor Commented:
You can choose.. some have limits at like 3, 5 or 20 gigs in a month (speed cut back to something like 56k)
others have $20 for 200meg... thats WAY too low, id go through that with ee alone.

What are prices like over there.. i had a look at those "hotspot" providers (yes i know, it'll cost me much more) and it seemed to be about <pound>40</pound> a month.  I dont really want to install cable or get a land line for ADSL if im going to be there for only 4months, so it seemed like the way to go for me
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.