Return Value & ID from INSERT Stored Proc 
Author Message
 Return Value & ID from INSERT Stored Proc

I've been fighting with this problem for several days, making an
INSERT stored proc work from ADO.  This same process in RDO was quite
straight-forward, but ADO seems to want a lot more code to accomplish
the same task.


INSERT stored proc.  I also want the RETURN value back so the
front-end knows whether or not the  INSERT succeeded.  I've read
conflicting info on how to do this, but no examples that match exactly
what I'm trying to do.  I'll post some code below...maybe someone can
point me in the right direction.  Can post the stored proc code, too,
if that's necessary.

Thanks in advance!  John Ellard

Private Sub cmdSaveChanges_Click()
    Dim cmdSP               As New ADODB.Command
    Dim param0              As New ADODB.Parameter
    Dim param1              As New ADODB.Parameter
    Dim param2              As New ADODB.Parameter
    Dim param3              As New ADODB.Parameter
    Dim param4              As New ADODB.Parameter
    Dim param5              As New ADODB.Parameter
    Dim param6              As New ADODB.Parameter
    Dim param7              As New ADODB.Parameter
    Dim param8              As New ADODB.Parameter
    Dim rs                  As New ADODB.Recordset
    Dim vntOutput           As Variant
    Dim vntReturn           As Variant

    On Error GoTo errActionItem_Save

    cnMain.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;" _
        & "Persist Security Info=False;Initial Catalog=ActionItem;Data
Source=AUSEXG001"
    cnMain.Open

    With cmdSP
        Select Case ActionItem
                Case "ADD"
                        .CommandText =
"sp_insert_action_item_TESTPROC"
                        Case "EDIT"
                                .CommandText = "sp_update_action_item"
                Case Else
                        Exit Sub
         End Select
        .CommandType = adCmdStoredProc
        .ActiveConnection = cnMain
    End With

    With myActionItem
        Set param0 = cmdSP.CreateParameter("ReturnVal", adInteger,
adParamReturnValue)
        cmdSP.Parameters.Append param0
        Set param1 = cmdSP.CreateParameter("name", adVarChar,
adParamInput, 100)
        cmdSP.Parameters.Append param1
        param1.Value = .Name
        Set param2 = cmdSP.CreateParameter("action_type", adVarChar,
adParamInput, 20)
        cmdSP.Parameters.Append param2
        param2.Value = .ActionType
        Set param3 = cmdSP.CreateParameter("due_date", adDBTimeStamp,
adParamInput)
        cmdSP.Parameters.Append param3
        param3.Value = .DueDateTime
        Set param4 = cmdSP.CreateParameter("complete_yn",
adUnsignedTinyInt, adParamInput)
        cmdSP.Parameters.Append param4
        Select Case .Completed
            Case True
                param4.Value = -1
            Case False
                param4.Value = 0
        End Select
        Set param5 = cmdSP.CreateParameter("meeting_id", adInteger,
adParamInput)
        cmdSP.Parameters.Append param5
        param5.Value = .MeetingID
        Set param6 = cmdSP.CreateParameter("comments", adVarChar,
adParamInput, 250)
        cmdSP.Parameters.Append param6
        param6.Value = .Comments
        Set param7 = cmdSP.CreateParameter("Completed", adDBTimeStamp,
adParamInput)
        cmdSP.Parameters.Append param7
        param7.Value = .CompletedDateTime
        Set param8 = cmdSP.CreateParameter("ActionID", adInteger,
adParamOutput)
        cmdSP.Parameters.Append param8
    End With
    Set rs = cmdSP.Execute
    rs.Close    'Tried with and without closing the empty recordset
    vntReturn = cmdSP.Parameters(0)
    vntOutput = cmdSP.Parameters(8)

    If IsNull(param0) Then
        GoTo errActionItem_Save
    ElseIf param0 = 99 Then
        GoTo errActionItem_Save
    End If
    If IsNull(param8) Then
        GoTo errActionItem_Save
    Else
        vntOutput = param8
    End If

CleanUp:
'Recover resources used.
'    On Error Resume Next
    cnMain.Close
    Set param0 = Nothing
    Set param1 = Nothing
    Set param2 = Nothing
    Set param3 = Nothing
    Set param4 = Nothing
    Set param5 = Nothing
    Set param6 = Nothing
    Set param7 = Nothing
    Set param8 = Nothing
    Set rs = Nothing
    Set cmdSP = Nothing
    Exit Sub

errActionItem_Save:
    With Err
        MsgBox "ERROR NUMBER: " & CStr(.Number) & vbCrLf & "ERROR
DESCRIPTION: " & .Description _
            & vbCrLf & "ERROR SOURCE: " & .Source, vbExclamation +
vbOKOnly, "ERROR SAVING ACTION ITEM"
    End With
    GoTo CleanUp
End Sub



Sun, 15 Sep 2002 03:00:00 GMT  
 Return Value & ID from INSERT Stored Proc
Look at this snippet ..

dataComm.CommandText = spBuilder
dataComm.CommandType = adCmdStoredProc
Set dataComm.ActiveConnection = Application("dataConn")

adVarChar, adParamInput, 35, p1)

adVarChar, adParamInput, 35, p2)

adParamOutput, 4)

spBuilder is thh name of my stored procedure and I have already created the

end of my stored procedure, then I copy the parameter to session variable
with :


The key is ready!  Looks like you have too many Parameter Objects you may
want to try this to clear a Command Objects parameters:

For i = 0 to (dataComm.Parameters.Count - 1)
 dataComm.Parameters.Delete(0)
Next

Lemme know how it goes...

Quote:

>I've been fighting with this problem for several days, making an
>INSERT stored proc work from ADO.  This same process in RDO was quite
>straight-forward, but ADO seems to want a lot more code to accomplish
>the same task.


>INSERT stored proc.  I also want the RETURN value back so the
>front-end knows whether or not the  INSERT succeeded.  I've read
>conflicting info on how to do this, but no examples that match exactly
>what I'm trying to do.  I'll post some code below...maybe someone can
>point me in the right direction.  Can post the stored proc code, too,
>if that's necessary.

>Thanks in advance!  John Ellard

>Private Sub cmdSaveChanges_Click()
>    Dim cmdSP               As New ADODB.Command
>    Dim param0              As New ADODB.Parameter
>    Dim param1              As New ADODB.Parameter
>    Dim param2              As New ADODB.Parameter
>    Dim param3              As New ADODB.Parameter
>    Dim param4              As New ADODB.Parameter
>    Dim param5              As New ADODB.Parameter
>    Dim param6              As New ADODB.Parameter
>    Dim param7              As New ADODB.Parameter
>    Dim param8              As New ADODB.Parameter
>    Dim rs                  As New ADODB.Recordset
>    Dim vntOutput           As Variant
>    Dim vntReturn           As Variant

>    On Error GoTo errActionItem_Save

>    cnMain.ConnectionString = "Provider=SQLOLEDB.1;Integrated
>Security=SSPI;" _
>        & "Persist Security Info=False;Initial Catalog=ActionItem;Data
>Source=AUSEXG001"
>    cnMain.Open

>    With cmdSP
> Select Case ActionItem
> Case "ADD"
>             .CommandText =
>"sp_insert_action_item_TESTPROC"
>        Case "EDIT"
>             .CommandText = "sp_update_action_item"
> Case Else
> Exit Sub
>       End Select
>        .CommandType = adCmdStoredProc
>        .ActiveConnection = cnMain
>    End With

>    With myActionItem
>        Set param0 = cmdSP.CreateParameter("ReturnVal", adInteger,
>adParamReturnValue)
>        cmdSP.Parameters.Append param0
>        Set param1 = cmdSP.CreateParameter("name", adVarChar,
>adParamInput, 100)
>        cmdSP.Parameters.Append param1
>        param1.Value = .Name
>        Set param2 = cmdSP.CreateParameter("action_type", adVarChar,
>adParamInput, 20)
>        cmdSP.Parameters.Append param2
>        param2.Value = .ActionType
>        Set param3 = cmdSP.CreateParameter("due_date", adDBTimeStamp,
>adParamInput)
>        cmdSP.Parameters.Append param3
>        param3.Value = .DueDateTime
>        Set param4 = cmdSP.CreateParameter("complete_yn",
>adUnsignedTinyInt, adParamInput)
>        cmdSP.Parameters.Append param4
>        Select Case .Completed
>            Case True
>                param4.Value = -1
>            Case False
>                param4.Value = 0
>        End Select
>        Set param5 = cmdSP.CreateParameter("meeting_id", adInteger,
>adParamInput)
>        cmdSP.Parameters.Append param5
>        param5.Value = .MeetingID
>        Set param6 = cmdSP.CreateParameter("comments", adVarChar,
>adParamInput, 250)
>        cmdSP.Parameters.Append param6
>        param6.Value = .Comments
>        Set param7 = cmdSP.CreateParameter("Completed", adDBTimeStamp,
>adParamInput)
>        cmdSP.Parameters.Append param7
>        param7.Value = .CompletedDateTime
>        Set param8 = cmdSP.CreateParameter("ActionID", adInteger,
>adParamOutput)
>        cmdSP.Parameters.Append param8
>    End With
>    Set rs = cmdSP.Execute
>    rs.Close 'Tried with and without closing the empty recordset
>    vntReturn = cmdSP.Parameters(0)
>    vntOutput = cmdSP.Parameters(8)

>    If IsNull(param0) Then
>        GoTo errActionItem_Save
>    ElseIf param0 = 99 Then
>        GoTo errActionItem_Save
>    End If
>    If IsNull(param8) Then
>        GoTo errActionItem_Save
>    Else
>        vntOutput = param8
>    End If

>CleanUp:
>'Recover resources used.
>'    On Error Resume Next
>    cnMain.Close
>    Set param0 = Nothing
>    Set param1 = Nothing
>    Set param2 = Nothing
>    Set param3 = Nothing
>    Set param4 = Nothing
>    Set param5 = Nothing
>    Set param6 = Nothing
>    Set param7 = Nothing
>    Set param8 = Nothing
>    Set rs = Nothing
>    Set cmdSP = Nothing
>    Exit Sub

>errActionItem_Save:
>    With Err
>        MsgBox "ERROR NUMBER: " & CStr(.Number) & vbCrLf & "ERROR
>DESCRIPTION: " & .Description _
>            & vbCrLf & "ERROR SOURCE: " & .Source, vbExclamation +
>vbOKOnly, "ERROR SAVING ACTION ITEM"
>    End With
>    GoTo CleanUp
>End Sub



Sun, 15 Sep 2002 03:00:00 GMT  
 Return Value & ID from INSERT Stored Proc
Here's my stored proc:

SET ANSI_NULLS  ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER  OFF

CREATE PROCEDURE [sp_insert_action_item_TESTPROC]








AS

BEGIN TRANSACTION

INSERT INTO [ActionItem].[dbo].[Action_Item_Test_Table]
         ( [name],
         [action_type],
         [due_date],
         [complete_yn],
         [meeting_id],
         [comments],
         [CompletedDateTime] )

VALUES








        BEGIN
                ROLLBACK TRANSACTION
                PRINT "An error occurred Adding a New Action Item"

                RETURN(99)
        END
ELSE
        BEGIN

                        BEGIN
                                COMMIT TRANSACTION
                                PRINT "New Action Item Record Added
Successfully!"

                                RETURN(0)
                        END
                ELSE
                        BEGIN
                                ROLLBACK TRANSACTION
                                PRINT "New Action Item Record NOT
Added -- Insert Failed!!"

                                RETURN(9)
                        END
        END



Quote:
>Look at this snippet ..

>dataComm.CommandText = spBuilder
>dataComm.CommandType = adCmdStoredProc
>Set dataComm.ActiveConnection = Application("dataConn")

>adVarChar, adParamInput, 35, p1)

>adVarChar, adParamInput, 35, p2)

>adParamOutput, 4)

>spBuilder is thh name of my stored procedure and I have already created the

>end of my stored procedure, then I copy the parameter to session variable
>with :


>The key is ready!  Looks like you have too many Parameter Objects you may
>want to try this to clear a Command Objects parameters:

>For i = 0 to (dataComm.Parameters.Count - 1)
> dataComm.Parameters.Delete(0)
>Next

>Lemme know how it goes...


>>I've been fighting with this problem for several days, making an
>>INSERT stored proc work from ADO.  This same process in RDO was quite
>>straight-forward, but ADO seems to want a lot more code to accomplish
>>the same task.


>>INSERT stored proc.  I also want the RETURN value back so the
>>front-end knows whether or not the  INSERT succeeded.  I've read
>>conflicting info on how to do this, but no examples that match exactly
>>what I'm trying to do.  I'll post some code below...maybe someone can
>>point me in the right direction.  Can post the stored proc code, too,
>>if that's necessary.

>>Thanks in advance!  John Ellard

>>Private Sub cmdSaveChanges_Click()
>>    Dim cmdSP               As New ADODB.Command
>>    Dim param0              As New ADODB.Parameter
>>    Dim param1              As New ADODB.Parameter
>>    Dim param2              As New ADODB.Parameter
>>    Dim param3              As New ADODB.Parameter
>>    Dim param4              As New ADODB.Parameter
>>    Dim param5              As New ADODB.Parameter
>>    Dim param6              As New ADODB.Parameter
>>    Dim param7              As New ADODB.Parameter
>>    Dim param8              As New ADODB.Parameter
>>    Dim rs                  As New ADODB.Recordset
>>    Dim vntOutput           As Variant
>>    Dim vntReturn           As Variant

>>    On Error GoTo errActionItem_Save

>>    cnMain.ConnectionString = "Provider=SQLOLEDB.1;Integrated
>>Security=SSPI;" _
>>        & "Persist Security Info=False;Initial Catalog=ActionItem;Data
>>Source=AUSEXG001"
>>    cnMain.Open

>>    With cmdSP
>> Select Case ActionItem
>> Case "ADD"
>>             .CommandText =
>>"sp_insert_action_item_TESTPROC"
>>        Case "EDIT"
>>             .CommandText = "sp_update_action_item"
>> Case Else
>> Exit Sub
>>       End Select
>>        .CommandType = adCmdStoredProc
>>        .ActiveConnection = cnMain
>>    End With

>>    With myActionItem
>>        Set param0 = cmdSP.CreateParameter("ReturnVal", adInteger,
>>adParamReturnValue)
>>        cmdSP.Parameters.Append param0
>>        Set param1 = cmdSP.CreateParameter("name", adVarChar,
>>adParamInput, 100)
>>        cmdSP.Parameters.Append param1
>>        param1.Value = .Name
>>        Set param2 = cmdSP.CreateParameter("action_type", adVarChar,
>>adParamInput, 20)
>>        cmdSP.Parameters.Append param2
>>        param2.Value = .ActionType
>>        Set param3 = cmdSP.CreateParameter("due_date", adDBTimeStamp,
>>adParamInput)
>>        cmdSP.Parameters.Append param3
>>        param3.Value = .DueDateTime
>>        Set param4 = cmdSP.CreateParameter("complete_yn",
>>adUnsignedTinyInt, adParamInput)
>>        cmdSP.Parameters.Append param4
>>        Select Case .Completed
>>            Case True
>>                param4.Value = -1
>>            Case False
>>                param4.Value = 0
>>        End Select
>>        Set param5 = cmdSP.CreateParameter("meeting_id", adInteger,
>>adParamInput)
>>        cmdSP.Parameters.Append param5
>>        param5.Value = .MeetingID
>>        Set param6 = cmdSP.CreateParameter("comments", adVarChar,
>>adParamInput, 250)
>>        cmdSP.Parameters.Append param6
>>        param6.Value = .Comments
>>        Set param7 = cmdSP.CreateParameter("Completed", adDBTimeStamp,
>>adParamInput)
>>        cmdSP.Parameters.Append param7
>>        param7.Value = .CompletedDateTime
>>        Set param8 = cmdSP.CreateParameter("ActionID", adInteger,
>>adParamOutput)
>>        cmdSP.Parameters.Append param8
>>    End With
>>    Set rs = cmdSP.Execute
>>    rs.Close 'Tried with and without closing the empty recordset
>>    vntReturn = cmdSP.Parameters(0)
>>    vntOutput = cmdSP.Parameters(8)

>>    If IsNull(param0) Then
>>        GoTo errActionItem_Save
>>    ElseIf param0 = 99 Then
>>        GoTo errActionItem_Save
>>    End If
>>    If IsNull(param8) Then
>>        GoTo errActionItem_Save
>>    Else
>>        vntOutput = param8
>>    End If

>>CleanUp:
>>'Recover resources used.
>>'    On Error Resume Next
>>    cnMain.Close
>>    Set param0 = Nothing
>>    Set param1 = Nothing
>>    Set param2 = Nothing
>>    Set param3 = Nothing
>>    Set param4 = Nothing
>>    Set param5 = Nothing
>>    Set param6 = Nothing
>>    Set param7 = Nothing
>>    Set param8 = Nothing
>>    Set rs = Nothing
>>    Set cmdSP = Nothing
>>    Exit Sub

>>errActionItem_Save:
>>    With Err
>>        MsgBox "ERROR NUMBER: " & CStr(.Number) & vbCrLf & "ERROR
>>DESCRIPTION: " & .Description _
>>            & vbCrLf & "ERROR SOURCE: " & .Source, vbExclamation +
>>vbOKOnly, "ERROR SAVING ACTION ITEM"
>>    End With
>>    GoTo CleanUp
>>End Sub



Sun, 15 Sep 2002 03:00:00 GMT  
 Return Value & ID from INSERT Stored Proc

and it looks as if you're using return values  ... do you have acceptors for
that in your front end?

eg: dataComm.Parameters.Append dataComm.CreateParameter ("ReturnCode",
adInteger, adParamReturnValue)

If all else fails remove the transaction elements and see if you can get it
to work, you will then receive more descriptive error messages.

Quote:

>Here's my stored proc:

>SET ANSI_NULLS  ON
>SET NOCOUNT ON
>SET QUOTED_IDENTIFIER  OFF

>CREATE PROCEDURE [sp_insert_action_item_TESTPROC]








>AS

>BEGIN TRANSACTION

>INSERT INTO [ActionItem].[dbo].[Action_Item_Test_Table]
> ( [name],
> [action_type],
> [due_date],
> [complete_yn],
> [meeting_id],
> [comments],
> [CompletedDateTime] )

>VALUES








> BEGIN
> ROLLBACK TRANSACTION
> PRINT "An error occurred Adding a New Action Item"

> RETURN(99)
> END
>ELSE
> BEGIN

> BEGIN
> COMMIT TRANSACTION
> PRINT "New Action Item Record Added
>Successfully!"

> RETURN(0)
> END
> ELSE
> BEGIN
> ROLLBACK TRANSACTION
> PRINT "New Action Item Record NOT
>Added -- Insert Failed!!"

> RETURN(9)
> END
> END



>>Look at this snippet ..

>>dataComm.CommandText = spBuilder
>>dataComm.CommandType = adCmdStoredProc
>>Set dataComm.ActiveConnection = Application("dataConn")

>>adVarChar, adParamInput, 35, p1)

>>adVarChar, adParamInput, 35, p2)

>>adParamOutput, 4)

>>spBuilder is thh name of my stored procedure and I have already created
the

>>end of my stored procedure, then I copy the parameter to session variable
>>with :


>>The key is ready!  Looks like you have too many Parameter Objects you may
>>want to try this to clear a Command Objects parameters:

>>For i = 0 to (dataComm.Parameters.Count - 1)
>> dataComm.Parameters.Delete(0)
>>Next

>>Lemme know how it goes...


>>>I've been fighting with this problem for several days, making an
>>>INSERT stored proc work from ADO.  This same process in RDO was quite
>>>straight-forward, but ADO seems to want a lot more code to accomplish
>>>the same task.


>>>INSERT stored proc.  I also want the RETURN value back so the
>>>front-end knows whether or not the  INSERT succeeded.  I've read
>>>conflicting info on how to do this, but no examples that match exactly
>>>what I'm trying to do.  I'll post some code below...maybe someone can
>>>point me in the right direction.  Can post the stored proc code, too,
>>>if that's necessary.

>>>Thanks in advance!  John Ellard

>>>Private Sub cmdSaveChanges_Click()
>>>    Dim cmdSP               As New ADODB.Command
>>>    Dim param0              As New ADODB.Parameter
>>>    Dim param1              As New ADODB.Parameter
>>>    Dim param2              As New ADODB.Parameter
>>>    Dim param3              As New ADODB.Parameter
>>>    Dim param4              As New ADODB.Parameter
>>>    Dim param5              As New ADODB.Parameter
>>>    Dim param6              As New ADODB.Parameter
>>>    Dim param7              As New ADODB.Parameter
>>>    Dim param8              As New ADODB.Parameter
>>>    Dim rs                  As New ADODB.Recordset
>>>    Dim vntOutput           As Variant
>>>    Dim vntReturn           As Variant

>>>    On Error GoTo errActionItem_Save

>>>    cnMain.ConnectionString = "Provider=SQLOLEDB.1;Integrated
>>>Security=SSPI;" _
>>>        & "Persist Security Info=False;Initial Catalog=ActionItem;Data
>>>Source=AUSEXG001"
>>>    cnMain.Open

>>>    With cmdSP
>>> Select Case ActionItem
>>> Case "ADD"
>>>             .CommandText =
>>>"sp_insert_action_item_TESTPROC"
>>>        Case "EDIT"
>>>             .CommandText = "sp_update_action_item"
>>> Case Else
>>> Exit Sub
>>>       End Select
>>>        .CommandType = adCmdStoredProc
>>>        .ActiveConnection = cnMain
>>>    End With

>>>    With myActionItem
>>>        Set param0 = cmdSP.CreateParameter("ReturnVal", adInteger,
>>>adParamReturnValue)
>>>        cmdSP.Parameters.Append param0
>>>        Set param1 = cmdSP.CreateParameter("name", adVarChar,
>>>adParamInput, 100)
>>>        cmdSP.Parameters.Append param1
>>>        param1.Value = .Name
>>>        Set param2 = cmdSP.CreateParameter("action_type", adVarChar,
>>>adParamInput, 20)
>>>        cmdSP.Parameters.Append param2
>>>        param2.Value = .ActionType
>>>        Set param3 = cmdSP.CreateParameter("due_date", adDBTimeStamp,
>>>adParamInput)
>>>        cmdSP.Parameters.Append param3
>>>        param3.Value = .DueDateTime
>>>        Set param4 = cmdSP.CreateParameter("complete_yn",
>>>adUnsignedTinyInt, adParamInput)
>>>        cmdSP.Parameters.Append param4
>>>        Select Case .Completed
>>>            Case True
>>>                param4.Value = -1
>>>            Case False
>>>                param4.Value = 0
>>>        End Select
>>>        Set param5 = cmdSP.CreateParameter("meeting_id", adInteger,
>>>adParamInput)
>>>        cmdSP.Parameters.Append param5
>>>        param5.Value = .MeetingID
>>>        Set param6 = cmdSP.CreateParameter("comments", adVarChar,
>>>adParamInput, 250)
>>>        cmdSP.Parameters.Append param6
>>>        param6.Value = .Comments
>>>        Set param7 = cmdSP.CreateParameter("Completed", adDBTimeStamp,
>>>adParamInput)
>>>        cmdSP.Parameters.Append param7
>>>        param7.Value = .CompletedDateTime
>>>        Set param8 = cmdSP.CreateParameter("ActionID", adInteger,
>>>adParamOutput)
>>>        cmdSP.Parameters.Append param8
>>>    End With
>>>    Set rs = cmdSP.Execute
>>>    rs.Close 'Tried with and without closing the empty recordset
>>>    vntReturn = cmdSP.Parameters(0)
>>>    vntOutput = cmdSP.Parameters(8)

>>>    If IsNull(param0) Then
>>>        GoTo errActionItem_Save
>>>    ElseIf param0 = 99 Then
>>>        GoTo errActionItem_Save
>>>    End If
>>>    If IsNull(param8) Then
>>>        GoTo errActionItem_Save
>>>    Else
>>>        vntOutput = param8
>>>    End If

>>>CleanUp:
>>>'Recover resources used.
>>>'    On Error Resume Next
>>>    cnMain.Close
>>>    Set param0 = Nothing
>>>    Set param1 = Nothing
>>>    Set param2 = Nothing
>>>    Set param3 = Nothing
>>>    Set param4 = Nothing
>>>    Set param5 = Nothing
>>>    Set param6 = Nothing
>>>    Set param7 = Nothing
>>>    Set param8 = Nothing
>>>    Set rs = Nothing
>>>    Set cmdSP = Nothing
>>>    Exit Sub

>>>errActionItem_Save:
>>>    With Err
>>>        MsgBox "ERROR NUMBER: " & CStr(.Number) & vbCrLf & "ERROR
>>>DESCRIPTION: " & .Description _
>>>            & vbCrLf & "ERROR SOURCE: " & .Source, vbExclamation +
>>>vbOKOnly, "ERROR SAVING ACTION ITEM"
>>>    End With
>>>    GoTo CleanUp
>>>End Sub



Sun, 15 Sep 2002 03:00:00 GMT  
 Return Value & ID from INSERT Stored Proc
Thanks for all your assistance!  I found a working example on an ADO FAQ URL
someone posted here.  I was able to get the Insert working, and get back the

the SP more robust...I had to remove the transaction commands and the error
checking, but I'm getting there.  Thanks again for all your help!!  John
Quote:


>and it looks as if you're using return values  ... do you have acceptors for
>that in your front end?

>eg: dataComm.Parameters.Append dataComm.CreateParameter ("ReturnCode",
>adInteger, adParamReturnValue)

>If all else fails remove the transaction elements and see if you can get it
>to work, you will then receive more descriptive error messages.


>>Here's my stored proc:

>>SET ANSI_NULLS  ON
>>SET NOCOUNT ON
>>SET QUOTED_IDENTIFIER  OFF

>>CREATE PROCEDURE [sp_insert_action_item_TESTPROC]








>>AS

>>BEGIN TRANSACTION

>>INSERT INTO [ActionItem].[dbo].[Action_Item_Test_Table]
>> ( [name],
>> [action_type],
>> [due_date],
>> [complete_yn],
>> [meeting_id],
>> [comments],
>> [CompletedDateTime] )

>>VALUES








>> BEGIN
>> ROLLBACK TRANSACTION
>> PRINT "An error occurred Adding a New Action Item"

>> RETURN(99)
>> END
>>ELSE
>> BEGIN

>> BEGIN
>> COMMIT TRANSACTION
>> PRINT "New Action Item Record Added
>>Successfully!"

>> RETURN(0)
>> END
>> ELSE
>> BEGIN
>> ROLLBACK TRANSACTION
>> PRINT "New Action Item Record NOT
>>Added -- Insert Failed!!"

>> RETURN(9)
>> END
>> END



>>>Look at this snippet ..

>>>dataComm.CommandText = spBuilder
>>>dataComm.CommandType = adCmdStoredProc
>>>Set dataComm.ActiveConnection = Application("dataConn")

>>>adVarChar, adParamInput, 35, p1)

>>>adVarChar, adParamInput, 35, p2)

>>>adParamOutput, 4)

>>>spBuilder is thh name of my stored procedure and I have already created
>the

>>>end of my stored procedure, then I copy the parameter to session variable
>>>with :


>>>The key is ready!  Looks like you have too many Parameter Objects you may
>>>want to try this to clear a Command Objects parameters:

>>>For i = 0 to (dataComm.Parameters.Count - 1)
>>> dataComm.Parameters.Delete(0)
>>>Next

>>>Lemme know how it goes...


>>>>I've been fighting with this problem for several days, making an
>>>>INSERT stored proc work from ADO.  This same process in RDO was quite
>>>>straight-forward, but ADO seems to want a lot more code to accomplish
>>>>the same task.


>>>>INSERT stored proc.  I also want the RETURN value back so the
>>>>front-end knows whether or not the  INSERT succeeded.  I've read
>>>>conflicting info on how to do this, but no examples that match exactly
>>>>what I'm trying to do.  I'll post some code below...maybe someone can
>>>>point me in the right direction.  Can post the stored proc code, too,
>>>>if that's necessary.

>>>>Thanks in advance!  John Ellard

>>>>Private Sub cmdSaveChanges_Click()
>>>>    Dim cmdSP               As New ADODB.Command
>>>>    Dim param0              As New ADODB.Parameter
>>>>    Dim param1              As New ADODB.Parameter
>>>>    Dim param2              As New ADODB.Parameter
>>>>    Dim param3              As New ADODB.Parameter
>>>>    Dim param4              As New ADODB.Parameter
>>>>    Dim param5              As New ADODB.Parameter
>>>>    Dim param6              As New ADODB.Parameter
>>>>    Dim param7              As New ADODB.Parameter
>>>>    Dim param8              As New ADODB.Parameter
>>>>    Dim rs                  As New ADODB.Recordset
>>>>    Dim vntOutput           As Variant
>>>>    Dim vntReturn           As Variant

>>>>    On Error GoTo errActionItem_Save

>>>>    cnMain.ConnectionString = "Provider=SQLOLEDB.1;Integrated
>>>>Security=SSPI;" _
>>>>        & "Persist Security Info=False;Initial Catalog=ActionItem;Data
>>>>Source=AUSEXG001"
>>>>    cnMain.Open

>>>>    With cmdSP
>>>> Select Case ActionItem
>>>> Case "ADD"
>>>>             .CommandText =
>>>>"sp_insert_action_item_TESTPROC"
>>>>        Case "EDIT"
>>>>             .CommandText = "sp_update_action_item"
>>>> Case Else
>>>> Exit Sub
>>>>       End Select
>>>>        .CommandType = adCmdStoredProc
>>>>        .ActiveConnection = cnMain
>>>>    End With

>>>>    With myActionItem
>>>>        Set param0 = cmdSP.CreateParameter("ReturnVal", adInteger,
>>>>adParamReturnValue)
>>>>        cmdSP.Parameters.Append param0
>>>>        Set param1 = cmdSP.CreateParameter("name", adVarChar,
>>>>adParamInput, 100)
>>>>        cmdSP.Parameters.Append param1
>>>>        param1.Value = .Name
>>>>        Set param2 = cmdSP.CreateParameter("action_type", adVarChar,
>>>>adParamInput, 20)
>>>>        cmdSP.Parameters.Append param2
>>>>        param2.Value = .ActionType
>>>>        Set param3 = cmdSP.CreateParameter("due_date", adDBTimeStamp,
>>>>adParamInput)
>>>>        cmdSP.Parameters.Append param3
>>>>        param3.Value = .DueDateTime
>>>>        Set param4 = cmdSP.CreateParameter("complete_yn",
>>>>adUnsignedTinyInt, adParamInput)
>>>>        cmdSP.Parameters.Append param4
>>>>        Select Case .Completed
>>>>            Case True
>>>>                param4.Value = -1
>>>>            Case False
>>>>                param4.Value = 0
>>>>        End Select
>>>>        Set param5 = cmdSP.CreateParameter("meeting_id", adInteger,
>>>>adParamInput)
>>>>        cmdSP.Parameters.Append param5
>>>>        param5.Value = .MeetingID
>>>>        Set param6 = cmdSP.CreateParameter("comments", adVarChar,
>>>>adParamInput, 250)
>>>>        cmdSP.Parameters.Append param6
>>>>        param6.Value = .Comments
>>>>        Set param7 = cmdSP.CreateParameter("Completed", adDBTimeStamp,
>>>>adParamInput)
>>>>        cmdSP.Parameters.Append param7
>>>>        param7.Value = .CompletedDateTime
>>>>        Set param8 = cmdSP.CreateParameter("ActionID", adInteger,
>>>>adParamOutput)
>>>>        cmdSP.Parameters.Append param8
>>>>    End With
>>>>    Set rs = cmdSP.Execute
>>>>    rs.Close 'Tried with and without closing the empty recordset
>>>>    vntReturn = cmdSP.Parameters(0)
>>>>    vntOutput = cmdSP.Parameters(8)

>>>>    If IsNull(param0) Then
>>>>        GoTo errActionItem_Save
>>>>    ElseIf param0 = 99 Then
>>>>        GoTo errActionItem_Save
>>>>    End If
>>>>    If IsNull(param8) Then
>>>>        GoTo errActionItem_Save
>>>>    Else
>>>>        vntOutput = param8
>>>>    End If

>>>>CleanUp:
>>>>'Recover resources used.
>>>>'    On Error Resume Next
>>>>    cnMain.Close
>>>>    Set param0 = Nothing
>>>>    Set param1 = Nothing
>>>>    Set param2 = Nothing
>>>>    Set param3 = Nothing
>>>>    Set param4 = Nothing
>>>>    Set param5 = Nothing
>>>>    Set param6 = Nothing
>>>>    Set param7 = Nothing
>>>>    Set param8 = Nothing
>>>>    Set rs = Nothing
>>>>    Set cmdSP = Nothing
>>>>    Exit Sub

>>>>errActionItem_Save:
>>>>    With Err
>>>>        MsgBox "ERROR NUMBER: " & CStr(.Number) & vbCrLf & "ERROR
>>>>DESCRIPTION: " & .Description _
>>>>            & vbCrLf & "ERROR SOURCE: " & .Source, vbExclamation +
>>>>vbOKOnly, "ERROR SAVING ACTION ITEM"
>>>>    End With
>>>>    GoTo CleanUp
>>>>End Sub



Mon, 16 Sep 2002 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Stored Procedure - Insert & Return Unique ID

2. Returning a Value to Access from a SQL Stored proc

3. Stored Proc Return Value

4. Stored Proc Return values / Output Params w ADO and SQL Server 7

5. VB/Sybase -- return value from stored proc?

6. Stored Proc Return values / Output Params w ADO and SQL Server 7

7. New in VBScript - I need to intercept a SQL Stored Proc Return value

8. Capturing Stored proc. return value

9. Stored Proc Return values / Output Params w ADO and SQL Server 7

10. Stored Proc Return values / Output Params w ADO and SQL Server 7

11. Return Values from Stored Proc

12. strange return value from a stored proc?

 

 
Powered by phpBB® Forum Software