Executing a stored procedure in another database using the CurrentProject.Connection

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

There are two code sections below. The first does not work. The second
works. This ADP connects to a database (DB1). There is a stored procedure
that the current user has rights to in the Master Database. We wanted to
execute it using the current project connection, rather than creating a
separate connection to execute it. Why does the second work, but the first
does not? The exact error message is:

"procedure 'sp_my_stored_proc' expects parameter '@SomeInputParam', which
was not supplied"

'***************** METHOD 1 - FAILS ***************

'Create the command object using the current connection.
Dim CmdFill As adodb.Command
Set CmdFill = New adodb.Command
Set CmdFill.ActiveConnection = CurrentProject.Connection
CmdFill.CommandType = adCmdStoredProc
CmdFill.CommandText = "sp_my_stored_proc"
CmdFill.Parameters.Item("@SomeInputParam").Value = strSomeString
CmdFill.Execute


'***************** METHOD 2 - SUCCEEDS ***************

Dim cnn As adodb.Connection
Set cnn = CurrentProject.Connection
cnn.sp_my_stored_proc strSomeString

'***************** END CODE SAMPLE ******************
 
Parameters of ADO command don't appear just because you specified
CommandText. You have to either create the parameter(s) explicitly, or use
Parameters.Refresh, so the client goes into the database and gets the
information about the parameters.

However, in this particular case the latter wouldn't work because of the
limitation of ADO and of the undocumented stored procedure ADO uses to get
the information about the parameters. So, only the 1st way remains:

CmdFill.Parameters.Append CmdFill.CreateParameter("@SomeInputParam",
adVarChar, adParamInput, 50 , strSomeString)
CmdFill.Execute

Alternatively, you can specify the parameter directly in the Cmdfill.Execute:

CmdFill.Execute Parameters:=strSomeString

Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com


M> There are two code sections below. The first does
M> not work. The second works. This ADP connects to
M> a database (DB1). There is a stored procedure that
M> the current user has rights to in the Master
M> Database. We wanted to execute it using the
M> current project connection, rather than creating a
M> separate connection to execute it. Why does the
M> second work, but the first does not? The exact
M> error message is:

M> "procedure 'sp_my_stored_proc' expects parameter
M> '@SomeInputParam', which was not supplied"

M> '***************** METHOD 1 - FAILS ***************

M> 'Create the command object using the current
M> connection.
M> Dim CmdFill As adodb.Command
M> Set CmdFill = New adodb.Command
M> Set CmdFill.ActiveConnection =
M> CurrentProject.Connection
M> CmdFill.CommandType = adCmdStoredProc
M> CmdFill.CommandText = "sp_my_stored_proc"
M>
M> CmdFill.Parameters.Item("@SomeInputParam").Value =
M> strSomeString
M> CmdFill.Execute

M> '***************** METHOD 2 - SUCCEEDS
M> ***************

M> Dim cnn As adodb.Connection
M> Set cnn = CurrentProject.Connection
M> cnn.sp_my_stored_proc strSomeString

M> '***************** END CODE SAMPLE
M> ******************
 
-----Original Message-----
There are two code sections below. The first does not work. The second
works. This ADP connects to a database (DB1). There is a stored procedure
that the current user has rights to in the Master Database. We wanted to
execute it using the current project connection, rather than creating a
separate connection to execute it. Why does the second work, but the first
does not? The exact error message is:

"procedure 'sp_my_stored_proc' expects
Click to expand...
parameter '@SomeInputParam', which
was not supplied"

'***************** METHOD 1 - FAILS ***************

'Create the command object using the current connection.
Dim CmdFill As adodb.Command
Set CmdFill = New adodb.Command
Set CmdFill.ActiveConnection = CurrentProject.Connection
CmdFill.CommandType = adCmdStoredProc
CmdFill.CommandText = "sp_my_stored_proc"
CmdFill.Parameters.Item("@SomeInputParam").Value = strSomeString
CmdFill.Execute


'***************** METHOD 2 - SUCCEEDS ***************

Dim cnn As adodb.Connection
Set cnn = CurrentProject.Connection
cnn.sp_my_stored_proc strSomeString

'***************** END CODE SAMPLE ******************

Try this .....
Set Comm1 = Application.CurrentProject.Connection
With CmdFill
Click to expand...
.ActiveConnection = Comm1
.CommandText = "sp_my_stored_proc"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@SomeInputParm",
adVarChar, adParamInput, 5, XSomeValue)
Set Rst1 = .Execute()
End With
 
Back
Top