connection.execute 2

[link9]

Anyone know if it's possible to call the .execute method on a connection object and have it put the resulting value into a variable, instead of a recordset???

The SQL query that I have is a little different than most, and instead of returning a large recordset, the result is one number between 0 and 1 (i.e. -- .87)

What I've had to do thus far is make a recordset, and then open it regularly like this:

rs.open sql, con, 1, 1

and then just access the single value like so:

rs("topBox&quot


what I'd really like to do to save myself from having to create an object just to get one silly little value is something like this:

dim topBox
topBox = con.execute (sql)

so that the return value goes straight into the variable and bypasses the creation of a recordset --

Anyone know how to do that?

thx
Paul Prewett

 

[LnOrS]

Not so fast!!
when tou make set topBox = con.execute(sql), topBox became an instance of a Recordset. You have to use topBox as you use a normal Recordset, topBox("topBox&quot...

Regards,
Luís Silva

 

[link9]

So then, the answer is no???

I didn't want to use the 'set' keyword -- i.e. I don't want to make a recordset object...

I'm not sure how I could do this outside of using a stored procedure with a return value, but if there is a way... I sure would like to know what it is.

thx
paul

 

[LnOrS]

Well, I think the answer is no! If you want that con.execute will return someting it will return a recordset! I took a look to MSDN library and:
Syntax
For a row-returning Command:
Set recordset = command.Execute( RecordsAffected, Parameters, Options )

For a non–row-returning Command:
command.Execute RecordsAffected, Parameters, Options

Return Value
Returns a Recordset object reference.


Regards,
Luís Silva

 

[jonscott8]

Yes, you can do this. Read up on ADO Parameter objects. Presuming your SQL is a SP with no input/output parameters and only a return value, use something along these lines:

Set cmd = Server.CreateObject("ADODB.Command&quot
Set cmd.ActiveConnection = con
cmd.CommandText = SQL
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Execute
'The following contains your return value
cmd.Parameters(0)

See the following KB articles for better examples/explanations:
HOWTO: Refresh ADO Parameters Collection for a Stored Procedure

http://support.microsoft.com/support/kb/articles/Q174/2/23.ASP

HOWTO: Calling SQL Server Stored Procedures from ASP

http://support.microsoft.com/support/kb/articles/Q164/4/85.ASP

Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[jonscott8]

Sorry, I didnt realize you were attempting to use only the execute method of the connection object. So the answer to your question is no.

However, one point I would like to illustrate is that the Execute method of the connection object actually creates an on-the-fly command object which does the work of executing the command text and returning the results. After doing so, it is discarded, transparent to the developer. Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[link9]

Thank you, gentlemen, for your input.