Code running without any result
Hi,
I am running Access form to pass parameters to a stored procedure in sql server backend in the pubs database to update records. However, the code is working but the results are not. This means for some reason the code is not updating the data. I believe, it has to do with data type issues. I am not certain though. I would appreciate if someone can help me to figure out why the stored procedure is not running. Thanks
CODE:
Private Sub cmdRunProc1_Click()
Dim conn As ADODB.Connection
'Dim cmdUpdate As ADODB.Command
Dim lngRecs
Dim strType
Dim curPercent
Set conn = New ADODB.Connection
conn.Provider = "SQLOLEDB"
conn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=pubs;Data Source=USILDEK1-21001;User ID=sa;Password=alloyd;"
conn.Open
Set cmdUpdate = New ADODB.Command
cmdUpdate.CommandType = adCmdStoredProc
cmdUpdate.CommandText = "usp_UpdatePrices"
Set cmdUpdate.ActiveConnection = conn
' Get the form values
strType = Forms!frmParamPassing_mod!txtBookType
curPercent = Forms!frmParamPassing_mod!txtPercent
curPercent1 = CCur(Forms!frmParamPassing_mod!txtPercent)
' Add the parameters
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Type", adVarWChar, adParamInput, 12, strType)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Percent", adCurrency, adParamInput, , curPercent1)
' Execute the command
cmdUpdate.Execute lngRecs, , adExecuteNoRecords
' And finally tell the user what's happened
MsgBox ("Procedure complete. Records were updated")
Set conn = Nothing
Set cmdUpdate = Nothing
End Sub
STORED PROCEDURE CODE:
create procedure usp_updateprices
@type char(12),
@percent money
as
update titles
set price = price * ( 1 + @percent /100)
where type = @type
|
