Problems with Stored Procedure

[gizz]

Hi everyone here is my problem, Im using SQL 7 and Access Project. I
have a stroed procedure that has parameters and a ADO code that is
surposed to fill in the parameters in the stored procedure. The
problem is that when I double click on the stored procedure it ask for
the parameters. I want the stored procedure to display the results
from the append paramaters from ADO. Please help me. The over all
goal is to insert the results into a temp table.

Here is the code for the Stored Procedure:

Alter Procedure SPUpdateSubOrgT
(@param1 int,
@param2 varchar,
@param3 int Output)
As
Select SubOrg_ID, SubOrg, Org
From SubOrgT Inner Join OrgT on SubOrgT.Org_ID=OrgT.Org_Id
Where dbo.SubORgT.SubOrg_Id=@param3
And dbo.SubOrgT.SubOrg=@param2
And dbrgT.Org_ID=@param1
exec SPUpdateSubOrgT @param1,@param2,@param3 output
print @param3

And Here is the ADO Code:

Function UpdateSP()
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim cnn1 As ADODB.Connection
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter, par
As Parameters
Dim rst As Recordset
Dim strCnn As String



Set param1 = New ADODB.Parameter
Set param2 = New ADODB.Parameter
Set param3 = New ADODB.Parameter

Set cnn1 = New ADODB.Connection
strCnn = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
SECURITY INFO=FALSE;INITIAL CATALOG=NewCopy;DATA SOURCE=MSDE"
cnn1.Open strCnn
cnn1.CursorLocation = adUseClient

Set cmd = New ADODB.Command
cmd.CommandText = "SPUpdateSubOrgT"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 15

Set param1 = cmd.CreateParameter("@param1", adInteger, adParamInput,
4)
cmd.Parameters.Append param1
param1.Value = CapOrg

Set param2 = cmd.CreateParameter("@param2", adVarChar, adParamInput,
255)
cmd.Parameters.Append param2
param2.Value = CapSubOrg

Set param3 = cmd.CreateParameter("@param3", adInteger,
adParamInputOutput, 4)
cmd.Parameters.Append param3
param3.Value = RptSubOrgid

Set cmd.ActiveConnection = cnn1
Set rst = cmd.Execute

Set rst = New ADODB.Recordset
rst.Open "SubOrgT", cnn1, , , adCmdTable

Set param1 = Nothing
Set param2 = Nothing
Set param3 = Nothing
Set cmd = Nothing
Set rst = Nothing
Set cnn1 = Nothing
End Function

 

[bassguy]

Your post has been out there a few days so I figure my guess won't hurt....It has been a year since I have done any coding like yours...but it seems to me that I remember the oddity that the @ signs were the problem in the ado code...you may want to remove them....sorry I cannot be more help it has been a while.


bassguy

 

[foxdev]

Gizz, likewise I don't know the answer (I'm not an ADO coder), but I'll try to help:

The problem is that when I double click on the stored procedure it ask for the parameters.

If a popup is appearing asking for the parameters, then its definitely not a SQL Server problem, as SQLS won't do this. So, I don't think the crux of the problem is your stored procedure, but is instead with ADO. If you haven't already, you might try posting the question there, without all of the SP code.

Also, to prevent things like LOL from appearing in your code, wrap the code with "[TT] [/TT]" tags. Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause