Stored procedure problem

[swyatt]

I created this stored procedure that does not work when I call it in
VBA

CREATE PROCEDURE [close_FEEDBACK]
(@TRACKING_ID_1 [char],
@STATUS [char](10)
AS UPDATE [CUSTFEEDBACK].[dbo].[FEEDBACK]
SET [STATUS] = @STATUS
WHERE
( [TRACKING_ID] = @TRACKING_ID_1)
GO

Code:
-------------------------------------------------------------------------------

Private Sub cmdTestUpd_Click()
Dim cmdUpdate As New ADODB.Command
Dim con As New ADODB.Connection
Dim cmdtext As String
Dim lngCount As Long

con.ConnectionString = CurrentProject.BaseConnectionString
con.Open
With cmdUpdate
.CommandText = "close_FEEDBACK"
.CommandType = adCmdStoredProc
.NamedParameters = True
.Parameters.Append .CreateParameter("@TRACKING_ID_1", adChar,
adParamInput, 9)
.Parameters.Append .CreateParameter("@STATUS", adChar,
adParamInput, 10)
.ActiveConnection = con
End With
cmdUpdate.Execute lngCount, Array(Me.TRACKING_ID.Value,
Me.STATUS.Value)
con.Close
MsgBox lngCount
End Sub
-------------------------------------------------------------------------------


When I just write a text update query it works fine.

I receive no errors, the record just doesn't get updated.

Any thoughts or suggestions are appreciated.

 

[Graham R Seach]

Try this instead:

CREATE PROCEDURE [close_FEEDBACK]
(
@TRACKING_ID_1 CHAR(10),
@STATUS CHAR(10)
--Specify a length (actually, you'd be better to use
VARCHAR for both)
)
AS
UPDATE [CUSTFEEDBACK].[dbo].[FEEDBACK]
SET [STATUS] = @STATUS
WHERE TRACKING_ID = @TRACKING_ID_1
GO

Code:
----------------------------------------------------------------------------
---

Private Sub cmdTestUpd_Click()
Dim cmdUpdate As ADODB.Command
Dim con As ADODB.Connection
Dim lngCount As Long

Set cmdUpdate = New ADODB.Command
Set con = New ADODB.Connection

con.ConnectionString = CurrentProject.ConnectionString
con.Open

With cmdUpdate
.ActiveConnection = con
.CommandText = "close_FEEDBACK"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@TRACKING_ID_1") = Me.TRACKING_ID.Value
.Parameters("@Status") = Me.Status.Value
.Execute lngCount
End With

MsgBox lngCount

Set cmd = Nothing
con.Close
Set con = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia