Hi All: I have a table that the user can input values in a couple of columns, for some or all rows. When the click a button I’d like to loop through the rows of the table and execute a stored procedure for any rows that have new inputs in one or both of said columns. Here is the code I’m using:
import time
localtime = time.localtime(time.time())
table = event.source.parent.getComponent('Table')
pyTable = fpmi.dataset.toPyDataSet(table.data)
Dateid=event.source.parent.getComponent('Date').selectedValue
Distid=event.source.parent.getComponent('Distributor').selectedValue ########### Validate Data ############
for row in range(len(pyTable)):
EndingInvValue = table.data.getValueAt(row, 4)
SalesValue = table.data.getValueAt(row, 5)
BrandValue = table.data.getValueAt(row, 0)
PacksizeidValue = table.data.getValueAt(row, 3)
CurYear=localtime[0]
CurMonth=localtime[1]
# CurYear=fpmi.db.dateFormat(now(),yyyy)
# CurMonth=fpmi.db.dateFormat(now(),M)
if EndingInvValue.isdigit() <> 0 or SalesValue.isdigit() <> 0: #Validate data
cr = fpmi.db.runPrepStmt("EXEC dbo.usp_PMIDepletionsInsert (?,?,?,?,?,?,?,?)", [Distid,Dateid,CurYear,CurMonth,BrandValue,PacksizeidValue,EndingInvValue,SalesValue])
The error that pmi throws is
Gateway error 301:
SQL error for “Exec dbo.usp_PMIDepletionsInsert(?,?,?,?,?,?,?,?)”: Incorrect syntax near ‘@PO’.
For Query EXEC dbo.usp_PMIDepletionsINsert(?,?,?,?,?,?,?,?)
Error 1 out of 7
And finally, the sql server sproc is:
ALTER PROCEDURE usp_PMIDepletionsInsert
@distid VARCHAR(10)
,@Dateid VARCHAR(10)
,@CurYear VARCHAR(10)
,@CurMonth VARCHAR(10)
,@Brand CHAR(3)
,@Packsizeid VARCHAR(10)
,@EndingInv VARCHAR(10)= NULL
,@Sales VARCHAR(10) = NULL
AS
SET NOCOUNT ON
BEGIN
DECLARE @PRODID INT
SELECT @PRODID=PRODID FROM dimPRoducts WHERE Productbrandcode=@Brand AND Packagesizeid=@packsizeid
IF LEN(@EndingInv)=0 SET @EndingInv='0'
IF LEN(@Sales)=0 SET @Sales='0'
IF ABS(CONVERT(INT,@EndingInv))+ABS(CONVERT(INT,@Sales))>0
BEGIN
INSERT INTO tblDepletions_test
([AreaID]
,[ProdID]
,[FiscalYear]
,[AccountingPeriod]
,[EndingInv]
,[Sales]
,[DATEID])
VALUES(CONVERT(INT,@distid),
CONVERT(INT,@prodid)
,CONVERT(INT,@CurYear)
,CONVERT(INT,@CurMonth)
,CONVERT(INT,@EndingInv)
,CONVERT(INT,@Sales)
,CONVERT(INT,@Dateid))
END
END
SET NOCOUNT OFF
tia. d. lewis