Results 1 to 4 of 4

Thread: storedprocedure

  1. #1
    Join Date
    Feb 2007
    Posts
    1

    Unanswered: storedprocedure

    Hi
    I have created a stored procedure with name 'CREATEBULKIMPORTLOG' in a database of SQL server 5.0. The contents of stored procedure is,

    ALTER PROCEDURE [dbo].[CREATEBULKIMPORTLOG]

    @BUSINESSUNIT char(2)
    @NOTE varchar(50)
    AS
    DECLARE @BULKIMPORTID int
    BEGIN
    SET NOCOUNT ON;
    INSERT INTO BULKIMPORTLOG ([ID_BUSINESSUNIT],[NOTE], [STARTTIME]) VALUES (@BUSINESSUNIT,@NOTE,getdate())

    END
    SET @BULKIMPORTID = @@IDENTITY
    RETURN @BULKIMPORTID

    Now that is my question: How can I use my StoredProcedure in access by one command button with a stored procedure that requires arguments? I don't want the little edit boxes to appear and ask me. here is my access code:
    Private Sub Befehl0_Click()
    On Error GoTo Err_Befehl0_Click
    Dim stDocName As String
    stDocName = "dbo.CREATEBULKIMPORTLOG"
    DoCmd.OpenStoredProcedure stDocName, acViewNormal, acEdit

    Exit_Befehl0_Click:
    Exit Sub

    Err_Befehl0_Click:
    MsgBox Err.Description
    Resume Exit_Befehl0_Click
    End Sub

    I will be grateful if you can help me!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Private Sub Befehl0_Click(<PASS VARIABLES IN HERE>,<VARIABLE>)
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I've seen other examples of how to do this, but this is how I do it.
    Look at CreateParameter in the help screen for more info.

    Code:
    Dim cmd As New ADODB.Command, prm As ADODB.Parameter
    ...
    With cmd
       .ActiveConnection = [yourconnection]
       .CommandText="CREATEBULKIMPORTLOG"
       .CommandType=adCmdStoredProc
    End With
    ...
    'set your input parameters
    Set prm = cmd.CreateParameter("@businessunit", adVarChar, adParamInput, 10, "YourBusinessUnitTextValue")
    cmd.Parameters.Append prm
    ...
    'set the output parameter
    .Parameters.Append .CreateParameter("@retval", adDouble, adParamOutput, , 0)
    cmd.Parameters.Append prm
    cmd.Execute
    ....
    'retrieve the returned value
    intRetVal=NZ(cmd.parameters("@retval"),0)
    Inspiration Through Fermentation

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Sharry77
    I have created a stored procedure with name 'CREATEBULKIMPORTLOG' in a database of SQL server 5.0.
    Holy cow. Is that a typo?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •