Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing recordsets to variables to be displayed

Status
Not open for further replies.
ziggy00001

ziggy00001

Programmer
Mar 11, 2002
17
0
0
CA
Can anyone see anything wrong with this set of code.

I have tested the SQL Statement and it returns the correct Balance, which = $422.63.

Where I beleive I am faultering is passing the recordset to the variable.

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=Lileo1;User Id=sa;Password=sa;"

Set RS = Server.CreateObject("ADODB.Recordset")

SQL = "SELECT Balance FROM Voucher INNER JOIN VoucherEntry ON Voucher.ID = VoucherEntry.VoucherID LEFT JOIN Item ON Item.ID = Voucher.ItemID LEFT JOIN [Transaction] ON VoucherEntry.TransactionNumber = [Transaction].TransactionNumber LEFT JOIN Customer ON [Transaction].CustomerID = Customer.ID WHERE Customer.ID = " & CurrentCust & ""
RS.Open SQL, Conn

Custom2 = FormatCurrency(RS("Balance"))
txtCustom2.innerText = Custom2

TIA
Rob
 
Sort by date Sort by votes
Response.write your SQL.
Code: 
SQL = "SELECT Balance FROM Voucher INNER JOIN VoucherEntry ON Voucher.ID = VoucherEntry.VoucherID LEFT JOIN Item ON Item.ID = Voucher.ItemID LEFT JOIN [Transaction] ON VoucherEntry.TransactionNumber = [Transaction].TransactionNumber LEFT JOIN Customer ON [Transaction].CustomerID = Customer.ID WHERE Customer.ID = " & CurrentCust & ""

[b]response.write(SQL):response.end[/b]
 
Upvote 0 Downvote
I might be in the wrong forum,
I am using VBScript and calling a function that the SQL statement is embeded in.
Here is my complete page

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<link rel="stylesheet" type="text/css" href="style.css">
<script type="text/javascript" language="javascript" src="global.js"></script>
<title>Taskpad</title>
<OBJECT classid="clsid:44C4C3AC-D0F1-11D2-919D-006008C88FC3" height="0" id="qsBridge" style="LEFT: 0px; TOP: 0px" width="0">
<PARAM NAME="_ExtentX" VALUE="26">
<PARAM NAME="_ExtentY" VALUE="26">
</OBJECT>
<script language="VBScript">
<!--

Dim TimerID

Const customcaptionCustomerText1 = 1
Const customcaptionCustomerText2 = 2
Const customcaptionCustomerText3 = 3
Const customcaptionCustomerText4 = 4
Const customcaptionCustomerText5 = 5


' ------------------------------------
' displayVariables
' ------------------------------------

Function displayVariables
On Error Resume Next

Dim QSRules
Dim Entry

Dim Custom1
Dim Custom2
Dim Custom3
Dim Custom4
Dim CurrentCust
Dim VNumber

Dim RS
Dim SQL

Set QSRules = qsBridge.RequestQSRules()

Custom1 = ""
Custom2 = ""
Custom3 = ""
Custom4 = ""
Custom5 = ""
CurrentCust = ""
VNumber = ""
SQL = ""

Set Entry = Nothing
Set RS = Nothing


If Not QSRules Is Nothing Then

' This line is valid after version 1.07
Set Entry = QSRules.Transaction.Entries(QSRules.Transaction.CurrentEntryKey)

If Not Entry Is Nothing Then

txtDescription = Entry.Description
txtQuantity = Entry.Quantity
txtPrice = FormatCurrency(Entry.Price)
txtExtended = FormatCurrency(Entry.ExtendedPrice)

End If

txtStoreName.innerText = QSRules.Configuration.StoreName

If QSRules.Transaction.Customer.Loaded Then

CurrentCust = QSRules.Transaction.Customer.ID
Custom1 = FormatCurrency(QSRules.Transaction.Customer.AccountBalance)

Custom3 = QSRules.Transaction.Customer.CustomText3
Custom4 = QSRules.Transaction.Customer.CustomText4


Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=Lileo1;User Id=sa;Password=sa;"
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT Balance FROM Voucher INNER JOIN VoucherEntry ON Voucher.ID = VoucherEntry.VoucherID LEFT JOIN Item ON Item.ID = Voucher.ItemID LEFT JOIN [Transaction] ON VoucherEntry.TransactionNumber = [Transaction].TransactionNumber LEFT JOIN Customer ON [Transaction].CustomerID = Customer.ID WHERE Customer.ID = " & CurrentCust & ""



RS.Open SQL, Conn
Custom2 = RS("Balance")

RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing

End If

End If

txtCustom1.innerText = Custom1
txtCustom2.innerText = Custom2
txtCustom3.innerText = Custom3
txtCustom4.innerText = Custom4

End Function


Function doStartup

' use a timer to run displayVariables() periodically every second

displayVariables
TimerID = window.setInterval("displayVariables()", 1000)

End Function

//-->
</script>
</head>
<body onload="doStartup()" scroll="no" STYLE="position:absolute; top:0px; left:0px; width:100%; height:100%; padding:10px; font:bold 13pt verdana; color:white;
filter:progid:DXImageTransform.Microsoft.Gradient(GradientType=1, StartColorStr='#8eb6d9', EndColorStr='#000000')">
<table border="0" align="left" width="880" STYLE="position:absolute; top:0px; left:0px">
<tr>
<td colspan="2" valign="top" STYLE="font-family:verdana; font-size:18px; position:absolute; left:10; width:874 ">
<span id="txtStoreName"></span>
</td>
</tr>
<tr>
<td width="874" colspan="2" valign="top" STYLE="height:1">
<img src="images/white_dot.gif" height="1px" width="45%">
</td>
</tr>
<tr>
<td align="left" valign="top" STYLE="font-family:verdana; font-size:12px; position:absolute; left:10; top:35; width:426 ">
<LI>
<span>Account Balance:</span>
<LI>
<span>Store Credit:</span>
<LI>
<span>Gift Certificate:</span>
<LI>
<span></span>
</td>
<td align="left" valign="top" STYLE="font-family:verdana; font-size:12px; position:absolute; left:150; top:35; width:444">
<span id="txtCustom1"></span><BR>
<span id="txtCustom2">></span><BR>
<span id="txtCustom3"></span><BR>
<span id="txtCustom4"></span><BR>
</td>
</tr>
</table>
<DIV ID="Div1" STYLE="position:absolute; bottom:-20; right:10 ; height:141; width:140; z-index: 2; filter:progid:DXImageTransform.Microsoft.Alpha(opacity=15);">
<img src="images/pos_symbol.gif" width="140" height="141"></DIV>
<DIV ID="Div2" STYLE="position:absolute; bottom:5; right:40 ; height:38; width:215; z-index: 1; filter:progidDXImageTransform.Microsoft.AlphaImageLoader(srcimageslogotype.png;"></DIV>
</body>
</html>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
100
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
93
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
79
leifoet
leifoet
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
221
GriffMG
GriffMG
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
218
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top