How to update record using stored procedure & vb6?

[coolwater]

Can someopne help me on how to update a record using stored procedures & vb6? my code below doesnt work. instead of updating the record it adds new record. I cnt understand y?

CREATE PROCEDURE [dbo].[sp_update]  @TestID bigint, @First_Name varchar (50), @Middle_Name varchar(50), @Family_Name varchar(50) AS
 
UPDATE    tblTest
SET             tblTest.FirstName = @First_Name, tblTest.MiddleName = @Middle_Name, tblTest.FamilyName = @Family_Name
WHERE     (tblTest.TestID = @TestID)
GO

Private Function UpdateRecordset(mRecNO As Long)
 
Dim sp_updateRec As ADODB.Command
Dim db As New ADODB.Connection
Dim pr_update(4) As Variant
Dim objParam As Long
 
db.Open myDB
db.CursorLocation = adUseClient
Set sp_update = New ADODB.Command
sp_updateRec.ActiveConnection = db
sp_updateRec.CommandType = adCmdStoredProc
sp_updateRec.CommandText = "sp_update"
 
'2nd solution
Set objParam = sp_ShowRec.CreateParameter("TestID", adBigInt, adParamInput, 8, mRecNO)
sp_updateRec.Parameters.Append objParam
 
sp_updateRec.Parameters.Append sp_updateRec.CreateParameter("@First_Name", adVarChar, adParamInput, 50, Text1.Text)
sp_updateRec.Parameters.Append sp_updateRec.CreateParameter("@Middle_Name", adVarChar, adParamInput, 50, Text2.Text)
sp_updateRec.Parameters.Append sp_updateRec.CreateParameter("@Last_Name", adVarChar, adParamInput, 50, Text3.Text)
 
sp_updateRec.Execute
 
db.Close
Set db = Nothing
Set sp_addnew = Nothing
 
End Function

[eranfox]

Hello coolwater,

Private Function UpdateRecordset(mRecNO As Long)
 
Dim [B]sp_updateRec[/B] As ADODB.Command
Dim db As New ADODB.Connection
Dim objParam As [B][COLOR=Blue]Parameter[/COLOR][/B]
 
db.Open myDB ' I guess this is your connection string etc...
db.CursorLocation = adUseClient
Set sp_updateRec = New ADODB.Command
[B]sp_updateRec[/B].ActiveConnection = db
[B]sp_updateRec[/B].CommandType = adCmdStoredProc
[B]sp_updateRec[/B].CommandText = "sp_update"
 
 
Set objParam = [B]sp_updateRec[/B].CreateParameter("TestID", adBigInt, adParamInput, 8, mRecNO)
[B]sp_updateRec[/B].Parameters.Append objParam
 
[B]sp_updateRec[/B].Parameters.Append [B]sp_updateRec[/B].CreateParameter("@First_Name", adVarChar, adParamInput, 50, Text1.Text)
[B]sp_updateRec[/B].Parameters.Append [B]sp_updateRec[/B].CreateParameter("@Middle_Name", adVarChar, adParamInput, 50, Text2.Text)
[B]sp_updateRec[/B].Parameters.Append [B]sp_updateRec[/B].CreateParameter("@Last_Name", adVarChar, adParamInput, 50, Text3.Text)
 
[B]sp_updateRec[/B].Execute
 
db.Close
Set db = Nothing
 
Set sp_updateRec = Nothing
 
End Function

I made some small changes to your code and tested it and it works!!

Best Regards,
ERAN