Solved

stored procedure syntax

Posted on 2014-10-21
8
159 Views
Last Modified: 2014-11-06
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 --

<%
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
%>

Open in new window


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
%>

Open in new window

--------------------------

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 ?
0
Comment
Question by:amucinobluedot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 

Author Comment

by:amucinobluedot
ID: 40394327
I tried replacing the value of the 'Paymntrecv' to be:

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
0
 

Author Comment

by:amucinobluedot
ID: 40394335
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
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 167 total points
ID: 40394380
Unless you are allowing people to pay a portion of what is owed, the rule of thumb is to never pass the price through the form to pay.    The only thing I would pass is the item number and qty.    Then in your SP, you can create a look up for the item id and grab the price that way.  Or create a recordset before the SP is called to look up the price.  


item_id = request.form("item")
qty        = request.form("qty")

' --- create recordset of items you sell called rsItems where the item's id is = to item_id
selling_price = rs("item_price")

' -- now update your SP
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:amucinobluedot
ID: 40394534
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.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40394830
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
0
 
LVL 33

Assisted Solution

by:Big Monty
Big Monty earned 333 total points
ID: 40396102
so we can take the same approach here that we took in the other question, split the IDs into an array and loop through them and insert the data into the database.

try this:

dim itemIDs : itemIDs = Request("itemID")
dim amounts : amounts = Request("amount")
dim arrItemID : arrItemID = Split( itemIDs, "," )
dim arrAmount : arrAmount = Split( amounts, "," )

<%while not BillingItems.eof
'execute SP

	for counter = 0 to UBound( arrItemID )

	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(arrItemID( counter ) <> "") then AddPaymentLines__ItemId = arrItemID( counter )

	Dim AddPaymentLines__ItemDesc
	AddPaymentLines__ItemDesc = "0"
	if(BillingItems("ItemDesc") <> "") then AddPaymentLines__ItemDesc = BillingItems("ItemDesc")

	Dim AddPaymentLines__PmtRecd
	AddPaymentLines__PmtRecd = "0"
	if(arrAmount( counter ) <> "") then AddPaymentLines__PmtRecd = arrAmount( counter )

	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
%>

Open in new window


I put each itemID in the AddPaymentLines__ItemId variable, and each amount in the AddPaymentLines__PmtRecd variables, if they belong elsewhere, just change the variables. This should get you going in the direction you need to be going without changing the SP.
0
 
LVL 33

Assisted Solution

by:Big Monty
Big Monty earned 333 total points
ID: 40402724
any luck with this?
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question