ASP, Stored Procedures, and you 2

[kaht]

(This example uses Jscript instead of VBscript)

Here's my situation.

When I call a stored procedure that returns a result this is how I usually pull the info:

var strConn = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=catalogname;User Id=userid;Password=password";
var oConn   = Server.CreateObject("ADODB.Connection");
oConn.Open(strConn);

var blah = oConn.Execute("storedProcedureName");
while (!blah.EOF) {
   Response.Write(blah("VP_NAME").value + " " + blah("LEVEL3_NAME").value + "<br />");
   blah.MoveNext;
}

This works fine for a stored procedure that only returns one recordset. However, if I had a stored procedure that produced the following output then I do not get all the information:

VP_NAME                                  LEVEL3_NAME                              
---------------------------------------- ---------------------------------------- 
mr vice president                        ms level 3 person                       
mr vice president                        ms level 3 person                       

VP_NAME                                  LEVEL3_NAME                              
---------------------------------------- ---------------------------------------- 
mr vice president                        ms level 3 person

With the actual test I did my page returned nothing. However, the first recordset returned in my test produced 0 rows, so I'm guessing it only read from the first recordset returned. Anybody know how I can retrieve all info returned from this stored procedure?

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[Sheco]

Did you trying playing with the NextRecordset method?

http://www.w3schools.com/ado/met_rs_nextrecordset.asp

PS: I like to put SET NOCOUNT ON in the top line of stored procedures that return recordsets.

 

[kaht]

would this work since I'm not technically using a recordset object?

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[mwolf00]

Hmm...

I use that construct all of the time (VB Script version) and I never have any problem. Are you sure your SP is working correctly?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[DotNetGnat]

i dont see anything wrong with the code...this is how it should look:

set r = c.Execute(sql)
do while not r.bof and not r.eof
	response.write r("exampleName") & "<BR>"
	r.movenext
loop

-DNG

 

[mwolf00]

DOH!!!

SET blah = oConn.Execute("storedProcedureName");

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[Sheco]

blah is a recordset object returned by the connection.execute method... just to make sure you can go to one of the working ones with a single resultset and check to see if you can reference other recordset properties like this:
[tt]
for each foo in blah.fields
response.write foo.name & "<br />"
next[/tt]

this will work if blah is actually referencing a recordset, which i believe it is... else how could you check its EOF ?

 

[mwolf00]

DNG - you beat me to it!

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[Sheco]

no yall he is not using VBScript in the example

 

[DotNetGnat]

Yes thats why i said, i dont see any error in the code...i was just showing him the comparable code for looping the recordset

-DNG

 

[kaht]

blah is a recordset object returned by the connection.execute method

Hmm... I didn't know it was returned as a recordset object. The reason I was unsure is because the very first test that I ran I did a Response.Write(blah.RecordCount) and got a value of -1. Although, since RecordCount is a recordset method I guess it would stand to make sense that it would be a recordset object..... Anyway, I'll try that out and see what happens.

If by chance I'm returning a variable number of recordsets, how would i check to make sure before moving to the next recordset that it exists first? Something like this?:

oRS = (oRS.NextRecordset()) ? oRS.NextRecordset : null

(And Sheco's right, I'm using Jscript like I mentioned in the first post)

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[kaht]

Running to lunch btw, be back in an hour.

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[Sheco]

If RecordCount wasnt a member of blah then you would have had some sort of error saying "invalid property or method" or somesuch.

The negative 1 is what the RecordCount method returns when you have an uncountable recordset. You got an uncountable recordset because it has the default forward-only firehose cursor type... so it cant move from start to end and count the records.. yeah its stupid I know.

 

[Sheco]

I dont know about checking to see if another recordset exists before moving to it but you should be able to call NextRecordset and then check the to make sure the returned object referefernce is not null/nothing before you attempt to call anything on it.

 

[Sheco]

How to make RecordCount property of the Recordset return the right value (not -1)?

 

[kaht]

ok, here's what I've come up with which works fine with one problem:

var strConn = "blahblahblah";
var oConn   = Server.CreateObject("ADODB.Connection");
var oRS   = Server.CreateObject("ADODB.Recordset");

oConn.Open(strConn);

oRS.ActiveConnection = oConn;
oRS.CursorType = 3;
oRS.LockType = 3;
oRS.CursorLocation = 3;

oRS.Source = "TRANSFERS..CP_TOP_TEN_VP 'suitsid', '2006-05-01', '2006-05-01'";
oRS.Open();
while (!oRS.EOF) {
   Response.Write(oRS.Fields("VP_NAME").value + " " + oRS.Fields("LEVEL3_NAME").value + "<br />");
   oRS.MoveNext;
}
oRS = oRS.NextRecordset();
while (!oRS.EOF) {
   Response.Write(oRS.Fields("VP_NAME").value + " " + oRS.Fields("LEVEL3_NAME").value + "<br />");
   oRS.MoveNext;
}

oRS = nothing;
oConn.Close();
oConn = nothing;

I tried my approach I mentioned above:
[tt]oRS = (oRS.NextRecordset()) ? oRS.NextRecordset : null[/tt]
and I got the following error message:

Current provider does not support returning multiple recordsets from a single execution

I googled this error message and found a bunch of potential solutions but nothing that was concrete. So, from one of the potential solutions I saw it said something about hitting the end of the recordsets. So, I took out the "last recordset detection" code that I came up with from the previous thread and tried exactly what I have pasted above (just to ensure that my provider does in fact support multiple recordsets from a single execution. This worked perfectly fine - returning the first 2 recordsets and printing the results to the page.

So.... I'm kinda stuck at the moment for how to detect that no more recordsets exist. I guess I could pull a query before the stored procedure is ran to determine the count, but that seems inefficient to me.

Any other suggestions are welcome.

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[Sheco]

oRS = (oRS.[red]NextRecordset[/red]()) ? [green]oRS.NextRecordset[/green] : null

When you call NextRecordset it actually moves to the next recordset so the [red]first[/red] time it is called you are on the correct second recordset... and the [green]second[/green] time it is called you go off the deep end.


so maybe something like this:

 [...]

oRS = oRS.NextRecordset();
[highlight]if (oRs != nothing)  // or maybe null ?[/highlight]
{ while (!oRS.EOF) 
  { Response.Write(oRS.Fields("VP_NAME").value + " " + oRS.Fields("LEVEL3_NAME").value + "<br />");
    oRS.MoveNext;
  }
}

oRS = nothing;
oConn.Close();
oConn = nothing;

 

[kaht]

Ok, I took your suggestion and tinkered and this worked:

oRS.Source = "TRANSFERS..CP_TOP_TEN_VP 'suitsid', '2006-05-01', '2006-05-01'";
oRS.Open();
while (oRS) {
   while (!oRS.EOF) {
      Response.Write(oRS.Fields("VP_NAME").value + " " + oRS.Fields("LEVEL3_NAME").value + "<br />");
      oRS.MoveNext;
   }
   oRS = oRS.NextRecordset();
}
oRS.Close();
Response.Write("the end");

Thanks for all the help. Sorry I turned into "that guy" that has a 20 post thread.

-kaht

[small]How spicy would you like your chang sauce? Oh man... I have no idea what's goin' on right now...[/small]

 

[Tarwn]

Don't be that guy...