Returning value from executed SQL query from ADO

Hi all, I've got some ADO VBA code like this:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With cmd
.ActiveConnection = ConnStr
.CommandText = "EXEC dbo.new_tbox @slide_id = " &
Forms("new_content").slide_id.Value & ", @shape_id = " &
Forms("new_content").Combo15.Value & ", @cnt_typ_id = " &
Forms("new_content").Combo0.Value

End With
rs.Open (cmd.Execute)

The SQL is correct, and when the command is run it returns the correct
value of a recordset.
(A 2 column table)

However, I can't get the return value of the query, it says that the
data-type is incorrect.

Could anyone tell me where I'm going wrong here?
By the way, the SQL query:

@slide_id int,
@shape_id int,
@cnt_typ_id int
AS

BEGIN
DECLARE @cnt_id int
INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id)

SET @cnt_id = @@IDENTITY

INSERT tbox_cnt VALUES(@cnt_id)
SELECT * FROM tbox_cnt WHERE tbox_cnt.cnt_id = @cnt_id

Re: Returning value from executed SQL query from ADO

It looks like you are trying to run a sql server stored procedure that
takes parameters. Not sure what you are trying to do. Here is a simple
example how to run a sql server stored procedure from Access that takes
parameters:

Dim cmd As New ADODB.Command
cmd.ActiveConnection = _
& "Provider=SQLOLEDB; Data Source=Serv1;" _
& "Database=TestDB;Trusted_Connection=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.commandText = "stp_Report_Label_Standard"
cmd.parameters("@userID").value = "joeshmo"
cmd.Execute
cmd.ActiveConnection.Close

You can expand this to use an ADODB Recordset object to return records
from a sql server.

Set RS = cmd.Execute

Rich

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

Re: Returning value from executed SQL query from ADO

On Oct 18, 12:27 pm, KoldKay wrote:
> Hi all, I've got some ADO VBA code like this:
>
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
> Dim rs As ADODB.Recordset
> Set rs = New ADODB.Recordset
> With cmd
> .ActiveConnection = ConnStr
> .CommandText = "EXEC dbo.new_tbox @slide_id = " &
> Forms("new_content").slide_id.Value & ", @shape_id = " &
> Forms("new_content").Combo15.Value & ", @cnt_typ_id = " &
> Forms("new_content").Combo0.Value
>
> End With
> rs.Open (cmd.Execute)
>
> The SQL is correct, and when the command is run it returns the correct
> value of a recordset.
> (A 2 column table)
>
> However, I can't get the return value of the query, it says that the
> data-type is incorrect.
>
> Could anyone tell me where I'm going wrong here?
> By the way, the SQL query:
>
> @slide_id int,
> @shape_id int,
> @cnt_typ_id int
> AS
>
> BEGIN
> DECLARE @cnt_id int
> INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id)
>
> SET @cnt_id = @@IDENTITY
>
> INSERT tbox_cnt VALUES(@cnt_id)
> SELECT * FROM tbox_cnt WHERE tbox_cnt.cnt_id = @cnt_id

I would use an OutPut Parameter to return the @@Identity value.
declaring the parameter
and later
using cmd.Parameters("NameofOutPutParameter").Value

but

Is tBoxCnt necessary?
Could you just
RETURN @@Identity
in which case you should be able to get its value with
cmd(0) ... if memory serves me correctly.