Call SP from command button

[Steven]

Hello using ADP, I have a form that takes user input in various text boxes
that are all unbound (not tied to rowsources) because the inserts need to be
dynamic. There is a checkbock that determines how the record(s) will be
inserted. (unchecked causes single table insert, checked performs a 2 table
insert) How do i code a command button's onclick event procedure to call a
store procedure to do this?
How should I code the SP to perform the double inserts (into different
tables) pulling the autonumer from the first insert to populate a field in
the second insert? Thank you.

 

[Malcolm Cook]

How do i code a command button's onclick event procedure to call a
store procedure to do this?

If you don't need a result/recordset, you can simply call the sp as though
it were a method on the access connection, like this:

Access.CurrentProject.AccessConnection.p_thisIsMyProc(somearg, anotherarg)

(the above assumes your stored proc is called 'p_thisIsMyProc' and takes two parameters).

How should I code the SP to perform the double inserts (into different
tables) pulling the autonumer from the first insert to populate a field in
the second insert? Thank you.

Assuming you want a single stored proc to perform the two inserts sequentially:

you will want to use the SCOPE_IDENTITY function to retrieve the value of the IDENTITY column from the first insert to use it in the
2nd insert. Look it up in SQL Server Books on-line (BOL).

 

[Norman Yuan]

Simply use ADO Command object, which passes the parameters, to execute
whatever SP you choose. Here is pseudo-code:


Private Sub CommandButton1_Click()

Dim cmd As ADO.Command
Dim pmt As ADO.Parameter

Set cmd=New ADO.Command
Set cmd.ActiveConnection=CurrentProject.Connection
cmd.CommandType=adCmdStoredProc
cmd.CommandText="MySPName"

''Create Paramters required by the SP
Set pmt=cmd.CreateParameter(....)
cmd.Parameters.Append pmt

''More parameters here

''Execute the SP
cmd.Execute

End

As for performing double insertion i a SP, it should be farly easy, like:

Create Procedure "MySPForInsertion"
(
@Para1 varchar(50),
@Para2 int,
...
)
AS

DECLARE @ID int

/*Assume Table1 has a Identity column*/
INSERT INTO Table1 (Col1,Col2,...)Values (@Para1,@Para2,...)
SET @ID=SCOPE_IDENTITY()

/*Insert to Table2
INSERT INTO Table2 (ParentIDCol,Col1,Col2,...) VALUES
(@ID,'Value1','Value2',...)

RETURN

You can also return the auto-generated ID back to ADO.Command object by
using output parameter, so the Command object will execute the SP and get
the new ID back in one round trip to the sql server.

 

[Steven]

Thanks for the reply, but i need a bit more hand-holding. When i click the
button i get the following Compile error: "User-defined type not defined" and
"cmd As ADO.Command" highlighted in the debug screen. Any suggestions, also,
how do i include the parameters; can you give me an example of an unbound
form textbox value being accepted as an sp's parameter? Thanks

Steven

 

[Sylvain Lafontaine]

It's ADODB.Command and ADODB.Parameter, not ADO.Command and ADO.Parameter.

For creating a parameter, it's something like:

cmd.Parameters.Append cmd.CreateParameter("@IdOrganisme", _
adInteger, adParamInput, , Me.TextBox_IdOrganisme.Value)

Look at the other posts in this newsgroup (or search Google) for more info;
otherwise, post again with a new thread.