NEED HELP WITH UPDATE PROCEDURE

[Lambro]

This is my stored procedure:

CREATE PROCEDURE [ScanTranSummary_Update]
(@ScanTranSummary_ID int,
@ScanTranSummary_CompanyCode char (2),
@ScanTranSummary_Location char(10),
@ScanTranSummary_ItemCode char(15),
@ScanTranSummary_PackQuantity char(6),
@ScanTranSummary_Quantity numeric(9),
@ScanTranSummary_Comments varchar(30),
@ScanTranSummary_TimeStamp char(18) )
AS
UPDATE
[tblScanTranSummary]
SET
ScanTranSummary_CompanyCode = @ScanTranSummary_CompanyCode,
ScanTranSummary_Location = @ScanTranSummary_Location,
ScanTranSummary_ItemCode = @ScanTranSummary_ItemCode,
ScanTranSummary_PackQuantity = @ScanTranSummary_PackQuantity,
ScanTranSummary_Quantity = @ScanTranSummary_Quantity,
ScanTranSummary_Comments= @ScanTranSummary_Comments,
ScanTranSummary_TimeStamp = @ScanTranSummary_TimeStamp
WHERE
ScanTranSummary_ID = @ScanTranSummary_ID
GO


This is my asp code:

strSQL = "EXEC ScanTranSummary_Update '" & Request.Form("ScanTranSummary_CompanyCode&quot & "', '" & Request.Form("ScanTranSummary_Location&quot & "', '" & Right(request("ScanTranSummary_ItemCode&quot, 15) & "', '" & replace(Request.Form("ScanTranSummary_PackQuantity&quot,"C","&quot & "', '" & Request.Form("ScanTranSummary_Quantity&quot & "', '" & Request.Form("ScanTranSummary_Comments&quot & "', '" & Request.Form("ScanTranSummary_TimeStamp&quot & "'"


It won't update my table tblScanTranSummary

 

[cathyw]

Hi Lambro,

I don't see a value for @ScanTranSummary_ID being passed in your exec statement (it is the first parameter listed in the stored proc though).

Hope that helps,
Cathy

 

[chriscorran]

Hi,

I find that its easier to read and easier to spot any mistakes or left out inputs if you don't concatenate your inputs and use the parameters.append way.

Also is you're updating records and you dont need to return a recordset use adExecuteNoRecords when you call execute. It's apparently more efficient as it doesnt create a recrdset. Thats what I heard anyway.

Take it easy.

Heres snippet.

Set cmdObj = New ADODB.Command

With cmdObj
.ActiveConnection = conn
.CommandText = "sp_yourProc"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@param1IN", adInteger, adParamInput, 4, var1)
.Parameters.Append .CreateParameter("@param2IN", adInteger, adParamInput, 4, var2)
.Parameters.Append .CreateParameter("@param3IN", adInteger, adParamInput, 4, var3)
.Parameters.Append .CreateParameter("@param4IN", adInteger, adParamInput, 4, var4)
.Parameters.Append .CreateParameter("@param5IN", adInteger, adParamInput, 4, var5)

.Execute , , adExecuteNoRecords

End With

 

[Lambro]

Ok chriscorran, I wentwith your suggestion.

I have one more question, I have two tables tblScannerTransactions is for all transactions, tblScanTranSummary is the summarized table of all of table tblScannerTransactions transactions using a trigger. When I edit a row in the tblScanTranSummary table, let's say I edit the quantity field that is 50 by now typing in 40. So basically I have 40 for a quantity. The row in table tblScanTranSummary is updated fine. I want the table tblScannerTransactions to record this update by adding a row to the table showing -10 in the quantity field.

How would I do this?

Thanks

 

[cathyw]

Hi Lambro,

Because you are using a trigger when transactions occur in your transaction table, I would be inclined to force your application to make ALL updates through the transaction table, and let the trigger always update the summary table. In this scenario, you would make your adjustment as a transaction of -10 units, instead of editing the summary table directly. The trigger would then update the quantity in the summary table from 50 to 40.


Hope this helps,
Cathy