ADODB connection and Cmd object - Whats Wrong?? 2

[hegartyjp]

Hi all.

I am afraid I have what is probably a very stupid problem.

I have never used the command object before but now find that I have to as I have atrted using ADO and cannot get SQL to pass any parameters back to access when I run my stored procedure.

If someone could please have a look at this code and tell me where I am going wrong I would be most grateful.


This code is just to run the stored procedure. I have not even got as far as passing the parameter back.





Dim connODBC As Connection
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command


Set cmd = New ADODB.Command

connODBC.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATABASE;Data Source=SERVER"


With cmd

.CommandText = "sp_tblNameLoc_SEL"
.Parameters.Refresh
.CommandType = adCmdStoredProc

End With


Set rst = cmd.Execute





Am I doing something idiotic or is it the connection.

I can call and execute spored procedures using this connection but maybe it needs to amended to use the COMMAND object.


Please help....

 

[hegartyjp]

Not to worry.

Was missing one line


.ActiveConnection = connODBC


Need to wake up...

 

[hegartyjp]

Hi All.

Well my joy is short lived


OK So I got the connection and command object working so that I could run a stored procedure. Now that I have moved on to try and get a parameter back I am once again lost.

How do I get the value back.

At the end of my stored procedure I have

SELECT @@IDENTITY as RecRef

Can anyone tell me how to get this value back.


This is my code......











Dim connODBC As Connection
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command


Set cmd = New ADODB.Command



connODBC.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATABASE;Data Source=SERVER"


strSQL = "sp_tblNameLoc_INS "



With cmd

.ActiveConnection = connODBC
.CommandText = strSQL
.Parameters.Refresh
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@VAL_NAME", adVarChar, adParamInput, 50, VAL_NAME)
.Parameters.Append .CreateParameter("@Val_LOC", adVarChar, adParamInput, 50, Val_LOC)
' .Parameters.Append .CreateParameter("@RecRef", adVarChar, adParamReturnValue, 50, RecReference)


End With

Set rst = cmd.Execute


cmd.Execute





Thanks for looking....Please help

 

[PWise]

your Stored proc should be like this

Alter Stored proc sp_tblNameLoc_INS 
@VAL_NAME varchar(xx),
@VAL_loc varchar(xx),


.......

SELECT @@IDENTITY As RecRef

you should call your sp like this

Dim connODBC As Connection
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

connODBC.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATABASE;Data Source=SERVER"

strSQL = "sp_tblNameLoc_INS "

With cmd
    .ActiveConnection = connODBC
    .CommandText = strSQL
    .CommandType = adCmdStoredProc
   
    .Parameters("@VAL_NAME")="xxxxx" 
    .Parameters("@VAL_loc")="yyy" 
         
    
End With

Set rst = cmd.Execute


cmd.Execute
msgbox "The new id num is " & rst!RecRef

 

[hegartyjp]

Thanks fella.

I still keep getting message that item was not found in collection when I try to read parameter.

Thanks for you efforts.

 

[PWise]

Please show me the code

 

[hegartyjp]

Hi. Hope you are here today. Had to leave after last post.

Again. Thanks for your efforts.



This is the code in access:

Private Sub TestIns_Click()

Dim strSQL As String
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim connODBC As New ADODB.Connection



connODBC.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATABASE;Data Source=SERVER"



Dim VAL_NAME As String
Dim Val_LOC As String

Val_LOC = "Test Loc3"
VAL_NAME = "Test Name"


strSQL = "sp_tblNameLoc_INS "



With cmd

.ActiveConnection = connODBC
.CommandText = strSQL
.Parameters.Refresh
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@VAL_NAME", adVarChar, adParamInput, 50, VAL_NAME)
.Parameters.Append .CreateParameter("@Val_LOC", adVarChar, adParamInput, 50, Val_LOC)
' .Parameters.Append .CreateParameter("@RecRef", adVarChar, adParamReturnValue, 50, RecReference)


End With




Set rst = cmd.Execute
cmd.Execute

MsgBox rst!IDNum

end sub











This is the Stored Procedure




CREATE PROCEDURE dbo.sp_tblNameLoc_Ins
(
@MyName varchar(50),
@MyLoc varchar(50)
)


AS
BEGIN
BEGIN TRAN


INSERT INTO dbo.tblNameLoc(
NameVal,
LocVal
)



VALUES
(
@MyName,
@MyLoc

)


Select @@IDENTITY as IDNum


IF (@@error!=0)
BEGIN
RAISERROR 20000 'dbo.sp_tblAccounts_INS: Cannot insert data into dbo.sp_tblAccounts '
ROLLBACK TRAN
RETURN(1)
END
COMMIT TRAN
END
GO

 

[danvlas]

Or... in stored procedure's definition include an output parameter:

CREATE PROCEDURE dbo.sp_tblNameLoc_Ins
(
@MyName varchar(50),
@MyLoc varchar(50),
@ID int out
)


AS
BEGIN
BEGIN TRAN


INSERT INTO dbo.tblNameLoc(
NameVal,
LocVal
)



VALUES
(
@MyName,
@MyLoc

)


Select @id = scope_identity()


IF (@@error!=0)
BEGIN
RAISERROR 20000 'dbo.sp_tblAccounts_INS: Cannot insert data into dbo.sp_tblAccounts '
ROLLBACK TRAN
RETURN(1)
END
COMMIT TRAN
END
GO


In VB(A):

With cmd
.ActiveConnection = connODBC
.CommandText = strSQL
.CommandType = adCmdStoredProc

.Parameters("@VAL_NAME")="xxxxx"
.Parameters("@VAL_loc")="yyy"
.Execute
NewIDVariable=.Parameters("@ID")

End With



HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[hegartyjp]

Thank you so much.

Thia has been driving me crazy.

Really appreciate your help.

I can get on with the rest of it now.

Cheers