04. april 2002 - 11:31Der er
4 kommentarer og 1 løsning
ADO vs ODBC - very fishy - help!
Hi there!
Have a quite bizzare problem that I hope someone can help me with - rather quickly too ;)
Well well, I decided to give an ADO-connection in the ASP-code instead of the regular ODBC-connection - since it apparently is quite much quicker. According to 4guysFromRolla this should be fairly easy - just change your connectionString from :
"DSN=hubbaHubba;UID=hubbaUser;PWD=hubbaPassword;"
to:
"Provider=SQLOLEDB; Data Source=11.11.11.200; Initial Catalog=hubbaHubbaDB; User ID=hubbaUser; Password=hubbaPassword;"
Ok, so everythings is fine - for a start that is. I "start it up", with the usual "objConn.Open(myConnectionString);" etc
But after trying to do a : "var rs = objConn.Execute("myStoredP 1,4,'whatever');"
followed by the standard way of putting things : "while (!rs.EOF) {"
it halts - telling me that the action is not allowed when the object is closed!?!!??! And I can guarantee that my objConn in no way is closed... I even tried it by removing the objConn.Close...
A closer inspection shows that the while-loop in itself is ok to perform - as long as I don't try to retrieve any data from the recordset, like "var firstName = String(rs("firstName"));"
Strange huh? Well even stranger it will become; Just to try things out I performed the following:
"objConn.Execute("select * from users")"
which worked just ok allowing me to retrieve and print data from the recordset.
So I subsequently tried the following:
"objConn.Execute("listAdminUsers 1")" - ("1" - being the single value to be submitted to the storedProcedure)
After executing the above, I was quite amazed to see that I actually could retrive data from the executed storedProcedure - leaving me with the conclusion that the recordsets delivered by my storedProcedures and ADO actually worksfine together.
Now the difference between the first storedProcedures and the last one, is that the first had much more arguments. Leading me to suspect that this is where my problem lies. Ie, the way that arguments/variables are submitted to the storedProcedure differs from ADO and ODBC.
Well well, I hope that somebody can offer any help =) I'm running an IIS on NT4 and a SQL2000 server on a seperate machine. Btw, using the Command-object is not an option - to many lines of code to rewrite ;)
I dette særtema ser vi på, hvordan cloud og AI bliver fundamentet for virksomhedernes digitale forretning, og hvordan de nye muligheder for automatisering og forretningsværdi kan udnyttes uden at miste overblik, sikkerhed og menneskelig kontrol.
I dont know if it will affect anything, but there are other possibilities when opening a connection and a recordset. When opening the connection you can use an other provider:
Well, hmmmm, not that well actually. The project as it is now - consists of something like 200 ASP-pages. I really don't have the oppurtunity to change my codeblocks from objConn.Execute to (for example) RS2.Open :(
But I really could use the connectin boost it would give to leave ODBC behind and adapt ADO... Any more suggestions?
I believe that there is a difference between the 2 way of connection and that the new connection dosent have the same permissions as the old one.
If you use this: "DSN=hubbaHubba" - then the permissions is granted to a kind of process that is created/running at the SERVER. (A DSN-process that communicate with youer ASP)
If you use this: "Provider=SQLOLEDB; Data Source=11.11.11.200; Initial Catalog=hubbaHubbaDB" then the permissions is granted to a connection-process that is running at your CLIENT.
and I believe that there is a slight difference when it comes to getting access to a recordset created by a Stored Procedure on the Server.
There have to be more difference between your 2 Stored Procedure than the parametres.
What kind of CursorType and CursorLocation do they use. ?
If the location is on the Server - who provide your client-process with the RS.Fields-information when you ask for it. (When the error come) It is not the Stored Procedure. The Stored Procedure deliver a recordset "somewhere private" witch no CLIENT-process can see because the location=Server. But a DSN-process have that permission. !
Maybe I'm wrong - but it was the best I could think of.
Thanks! I'll truy that one - it will most probably fix my problems =) Peace, Gemini
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
