Aleks
asked on
stored procedure syntax
I am using ASP/VBScript and MS SQL 2008
I am passing the following values to an ASP page:
<%=Request("itemid")%> --- the values passed in this example are :
<%=Request("amount")%> --- the values passed in this example are:
Then I create a recordset that will return records based on the 'itemids' passed on to it
-- Recordset --
Then I go through all the values of the recordset that is returned to insert into a table, looping through them:
-- Stored procedure --
This is all good, the SP works fine, the problem is that the 'amount' is not being inserted because I don't have a way of inserting it by using the stored procedure, so it inserts all the information correctly, but the amount is left at '0', and I need to loop the amount as well, so that when it inserts the first row it inserts also the first amount, then the next row, next amount.
How can I do this ?
I am passing the following values to an ASP page:
<%=Request("itemid")%> --- the values passed in this example are :
<%=Request("amount")%> --- the values passed in this example are:
Then I create a recordset that will return records based on the 'itemids' passed on to it
-- Recordset --
<%
Dim BillingItems__MMColParam
BillingItems__MMColParam = "0"
If (Request("itemid") <> "") Then
BillingItems__MMColParam = Request("itemid")
End If
%>
<%
Dim BillingItems
Dim BillingItems_cmd
Dim BillingItems_numRows
Set BillingItems_cmd = Server.CreateObject ("ADODB.Command")
BillingItems_cmd.ActiveConnection = MM_eimmigration_STRING
BillingItems_cmd.CommandText = "SELECT Id, CaseId, ItemId, ItemDesc, ItemlongDec, UnitCost, Qty, PmtRecd,((UnitCost * Qty)-PmtRecd) AS Balance FROM BillingLines WHERE id in (?) AND ((UnitCost * Qty)-PmtRecd) <>0"
BillingItems_cmd.Prepared = true
BillingItems_cmd.Parameters.Append BillingItems_cmd.CreateParameter("param1", 5, 1, -1, BillingItems__MMColParam) ' adDouble
Set BillingItems = BillingItems_cmd.Execute
BillingItems_numRows = 0
%>
Then I go through all the values of the recordset that is returned to insert into a table, looping through them:
-- Stored procedure --
<%while not BillingItems.eof
'execute SP
%>
<%
Dim AddPaymentLines__Caseid
AddPaymentLines__Caseid = "0"
if(BillingItems("CaseId") <> "") then AddPaymentLines__Caseid = BillingItems("CaseId")
Dim AddPaymentLines__BillsMainId
AddPaymentLines__BillsMainId = "0"
if(Session("PaymentId") <> "") then AddPaymentLines__BillsMainId = rs_topbillpymnt("MainPaymntId")
Dim AddPaymentLines__EntryBy
AddPaymentLines__EntryBy = "0"
if(Session("UserId") <> "") then AddPaymentLines__EntryBy = Session("UserId")
Dim AddPaymentLines__ItemId
AddPaymentLines__ItemId = "0"
if(BillingItems("Id") <> "") then AddPaymentLines__ItemId = BillingItems("Id")
Dim AddPaymentLines__ItemDesc
AddPaymentLines__ItemDesc = "0"
if(BillingItems("ItemDesc") <> "") then AddPaymentLines__ItemDesc = BillingItems("ItemDesc")
Dim AddPaymentLines__PmtRecd
AddPaymentLines__PmtRecd = "0"
if(BillingItems("PmtRecd") <> "") then AddPaymentLines__PmtRecd = BillingItems("PmtRecd")
Dim AddPaymentLines__Balance
AddPaymentLines__Balance = "0"
if(BillingItems("Balance") <> "") then AddPaymentLines__Balance = BillingItems("Balance")
Dim AddPaymentLines__UnitCost
AddPaymentLines__UnitCost = "0"
if(BillingItems("UnitCost") <> "") then AddPaymentLines__UnitCost = BillingItems("UnitCost")
%>
<%
set AddPaymentLines = Server.CreateObject("ADODB.Command")
AddPaymentLines.ActiveConnection = MM_eimmigration_STRING
AddPaymentLines.CommandText = "dbo.BDOTAddPaymentLines"
AddPaymentLines.Parameters.Append AddPaymentLines.CreateParameter("@RETURN_VALUE", 3, 4)
AddPaymentLines.Parameters.Append AddPaymentLines.CreateParameter("@Caseid", 3, 1,4,AddPaymentLines__Caseid)
AddPaymentLines.Parameters.Append AddPaymentLines.CreateParameter("@BillsMainId", 3, 1,4,AddPaymentLines__BillsMainId)
AddPaymentLines.Parameters.Append AddPaymentLines.CreateParameter("@EntryBy", 3, 1,4,AddPaymentLines__EntryBy)
AddPaymentLines.Parameters.Append AddPaymentLines.CreateParameter("@ItemId", 3, 1,4,AddPaymentLines__ItemId)
AddPaymentLines.Parameters.Append AddPaymentLines.CreateParameter("@ItemDesc", 200, 1,250,AddPaymentLines__ItemDesc)
AddPaymentLines.Parameters.Append AddPaymentLines.CreateParameter("@PmtRecd", 6, 1,8,AddPaymentLines__PmtRecd)
AddPaymentLines.Parameters.Append AddPaymentLines.CreateParameter("@Balance", 6, 1,8,AddPaymentLines__Balance)
AddPaymentLines.Parameters.Append AddPaymentLines.CreateParameter("@UnitCost", 6, 1,8,AddPaymentLines__UnitCost)
AddPaymentLines.CommandType = 4
AddPaymentLines.CommandTimeout = 0
AddPaymentLines.Prepared = true
AddPaymentLines.Execute()
%>
<%
BillingItems.movenext
wend
%>
--------------------------This is all good, the SP works fine, the problem is that the 'amount' is not being inserted because I don't have a way of inserting it by using the stored procedure, so it inserts all the information correctly, but the amount is left at '0', and I need to loop the amount as well, so that when it inserts the first row it inserts also the first amount, then the next row, next amount.
How can I do this ?
ASKER
I thought adding the whole page would be easier. The values being passed are:
'Itemid' and 'amount'
I use the itemid's to filter the "BillingItems" recordset and return the details of each item, but I can't tie the 'amount' to each item in that recordset by simply using the id to filter them out, is there a way to add the 'amount' to the records returned by the recordset ? This would solve the issue and I would not have to worry about modifying the SP.
Right now the recordset returns:
paymntid, caseid, entryby, etc ...
Would be great to append the amount at the end so that it returns something like:
paymntid, caseid, entryby, etc ..., amount
next record ...
This way I can take the value from the recordset and use my stored procedure.
page.txt
'Itemid' and 'amount'
I use the itemid's to filter the "BillingItems" recordset and return the details of each item, but I can't tie the 'amount' to each item in that recordset by simply using the id to filter them out, is there a way to add the 'amount' to the records returned by the recordset ? This would solve the issue and I would not have to worry about modifying the SP.
Right now the recordset returns:
paymntid, caseid, entryby, etc ...
Would be great to append the amount at the end so that it returns something like:
paymntid, caseid, entryby, etc ..., amount
next record ...
This way I can take the value from the recordset and use my stored procedure.
page.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am allowing people to pay for a portion of what is paid, hence the way it should work. If someone has a way of appending the amount it would be greatly appreciated.
are you trying to do this without modifying the stored procedure? if so, you would need to break out the amounts into an array, loop through them, then update the records that you just inserted with the correct amount. modifying the SP to accept another parameter (amount) would make things much more simpler. Let me know which direction you'd like to go in and I'll get you something to work off of in a little bit
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dim AddPaymentLines__PmtRecd
AddPaymentLines__PmtRecd = "0"
if(Request("amount") <> "") then AddPaymentLines__PmtRecd = Request("amount")
but of course, amount is : 15,50 because its not looping the values.
BTW, seems like I forgot to enter the values at the beginning:
Itemids = 4506, 4508
Amounts = 15, 50