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!

SQL recordset passing to variable for later display 1

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 = RS("Balance")

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

txtCustom2.innerText = Custom2



 
Sort by date Sort by votes
1) If any, comment out each On Error Resume Next lines
2) As you use Server.CreateObject, be sure your script is server side (as opposed to client side)
3) You may try this:
Const adOpenDynamic=2, adLockPessimistic=2, adCmdText=1
RS.Open SQL, Conn, adOpenDynamic, adLockPessimistic, adCmdText
If RS.EOF Then
Custom2 = 0
Else
Custom2 = RS("Balance")
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
why set txtCustom2.innerText rather than just saying document.write Custom2 in the appropriate place lower down the page.

(if you are working in the document header, your way may not even work, as txtCustom2 has not yet been loaded).

have you tested to see if Custom2 itself gets the right value? ( i.e. document.write "<script>alert('"&Custom2 &"');</script>" )
 
Upvote 0 Downvote
As you can see I have placed it in the head tag but it is because of the way the function is called.

Because this is embedded inside an application frame and every time a new customer is set within the application the function is being called.

This way does work as I can as I am able set values of the Custom1, Custom3 and Custom4 variables.

The only problem is in order to get the Voucher(i.e. Store Credit) amount from the DB is to create a new connection with the specified SQL statement.

I tried to leave as much info out as possible in order to make it easier to read.

If you have any other suggestions Nevermoor I would greatly appreciate your help, or anybody else for that matter.

NOTE:
PHV with your suggestion it is displaying the 0, which is strange as I am able to execute the SQL statement within Enterprise Manager. Also if I automatically set CurrentCust = QSRules.Transaction.Customer.ID which 48 within Enterprise Manager it returns 422.63, which is the customer I am using in the application.
I am able to set Custom2 = CurrentCust and it does display 48 as well.

Code: 
<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, adOpenDynamic, adLockPessimistic, adCmdText
		 If RS.EOF Then
		 	Custom2 = FormatCurrency(0)
		 Else
		 	Custom2 = FormatCurrency(RS("Balance"))
		 End If 
          	
     	 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"><%response.write(SQL):response.end%></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
What happens if you replace this:
On Error Resume Next
By this ?
Option Explicit

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
I get Error: Expected Statement Line 29
which is where I replaced On Error Resume Next with Option Explicit
 
Upvote 0 Downvote
Sorry.
Comment out the On Error Resume Next line
and put the Option Explicit declaration before the Dimr timerId line.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Now it is giving me a...
Runtime Error
Line: 83
Error: Variable undefined 'Server'
which is the line before...

Set Conn = Server.CreateObject("ADODB.Connection")
 
Upvote 0 Downvote
So, now you know why ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Actually I'm even more confused as to why I need to declare

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

as a variable?
Or am I completely missing something here?
 
Upvote 0 Downvote
Server is an object exposed by IIS and usable only in server side script, not client side. (I warned you in my first reply).
Depending on your security level you may try :
Set conn = CreateObject("ADODB.Connection")
and similarly for RS.
But I guess your connection string should contains the server name instead of (local).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Thank you very much for your patience and help PHV you have been a tremendous help.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

stable-x
  • Locked
  • Question
How to set/define a variable from two variables?
Replies
3
Views
133
stable-x
stable-x
EdScripts
  • Locked
  • Question
Using a Variable in the Run Method - I would sure like to be able to do this !
Replies
0
Views
72
EdScripts
EdScripts
elly00
  • Locked
  • Question
sql result
Replies
1
Views
61
strongm
strongm
Brianfree
  • Locked
  • Question
Excel Rows to Column 1
Replies
2
Views
135
Brianfree
Brianfree
Rick_Stanich
  • Locked
  • Question
Save xls file to cvs file 1
Replies
3
Views
114
Rick_Stanich
Rick_Stanich

Part and Inventory Search

Sponsor

Back
Top