executing store procedure from a form in a ADP project

[grmman]

How do you call a store proc from a form.
Thanks

Gary

 

[cmmrfrds]

Lets say a stored procedure was defined as such.
CREATE Procedure sp_ActivityDescription
@activityID int ,
@activityDescription varchar (100) output
As

Then
Data tab on the Form.
Record Source dbo.sp_ActivityDescription
Parameters @activityID=123

 

[grmman]

How do I call it from a command button.
I need the user to click on a button and this store proc needs to run.
Thanks

 

[cmmrfrds]

Example of using the command object.

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter, param4 As Parameter
begDate = #10/1/2001#
endDate = #10/31/2001#

' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn
''Set param1 = cmd.CreateParameter("Input", adDBDate, adParamInput)
Set param1 = cmd.CreateParameter(, adDBDate, adParamInput, , "10/1/2001")
cmd.Parameters.Append param1
param1.Value = begDate
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate
Set param3 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param3
param3.Value = 3
Set param4 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param4
param4.Value = 3

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_employeeRevenueProgress"
cmd.CommandType = adCmdStoredProc

' Execute command and return results to recordset

Set rst = cmd.Execute

 

[grmman]

I tryed this and I am getting and run-time error 3708.
Parameter object is improperly defined. Inconsistent or incompltee infor was provided.

here is my code maybe I typed something wrong
Thanks

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As ADODB.Parameter, param2 As ADODB.Parameter, param3 As ADODB.Parameter, param4 As ADODB.Parameter

' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn

Set param1 = cmd.CreateParameter("lanme", adVarChar, adParamInput)
*** THIS IS THE LINE IT COMES BACK WITH THE ERROR TO
cmd.Parameters.Append param1
*****
param1.Value = Me.lst_bp.Column(0)

Set param2 = cmd.CreateParameter("fname", adVarChar, adParamInput)
cmd.Parameters.Append param2
param2.Value = Me.lst_bp.Column(1)

Set param3 = cmd.CreateParameter("id", adSmallInt, adParamInput)
cmd.Parameters.Append param3
param3.Value = Me.lst_bp.Column(2)


' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.proc_insert_position"
cmd.CommandType = adCmdStoredProc

' Execute command and return results to recordset

Set rst = cmd.Execute

 

[cmmrfrds]

an adVarChar, since the length is undetermined must be given a length in the parameter definition.

Set param1 = cmd.CreateParameter("lanme", adVarChar, adParamInput, 25)

i.e a length of 25

 

[grmman]

THANK YOU

 

[T3leTech]

Using: Access 2000 ADP, SQL Server 2000.
I have a similar problem, but I have been trying to solve it using a stored procedure with the InputParameters passed in from a form. In other words, I don't have any VBA code running that fires the sproc. I use the sproc to populate a report (the report is based on the sproc) and I've set two variables in the InputParameters line of the report (@BeginDate DateTime, @EndDate DateTime) to the two fields on the form. This works great unless I only want to see one day's worth of data. I don't know how to strip off the time values in the datetime expression (which is what I assume the problem is--because when I exec the sproc in QueryAnalyzer with hard coded date-time values, it works fine). I have tried changing the data type to char, and then using Convert, and setting to only 8 characters (mm/dd/yy), but I get the same result. Do I have to create VBA code, using ADODB commands, like the ones listed above, to get this to work properly?

--Telephony Tech

 

[cmmrfrds]

Are you doing something like this. I would bump up the end date by 1 day and check for less than.

declare @mydate as datetime
declare @mydate1 as datetime
select @mydate = '09/14/1994 12:12:12'
select @mydate2 = '09/30/1994 15:15:15'
select @mydate2 = dateadd('d',+1,@mydate2)

select * from sales where
convert(varchar(10), my_date,101) >= @mydate and
convert(varchar(10), my_date,101) < @mydate2

But this should work too.
select * from sales where
convert(datetime, my_date,101) between
convert(datetime, @mydate,101)and
convert(datetime, @mydate2,101)

 

[T3leTech]

That is it...pretty much. I want the user to be able to enter the same date for the @BeginDate and @EndDate (to capture a single day's data) and also to be able to enter multiple dates (like from the 1st to the 15th). I don't want them to enter a time value, but I always want the data to be through the end of the day in @EndDate. In your example above, wouldn't it add one day so that information would be returned from October 1, 1994?

--Telephony Tech

 

[cmmrfrds]

Yes, I put that in to show how to do the less than instead of using between. Of course, for the between that would not be necessary.

As per the example, does this not work? If not, paste in your code.

 

[T3leTech]

Thanks! That works!

--Telephony Tech