currency conversion on retrieve record from sql table using asp

[omar999]

I have a sql table containing GBP prices and I was wondering if there is a way to pull out these prices, convert them to EURO prices and then to display them on a webpage as EURO prices?

is this even possible?

p.s I dont have a problem pulling out the prices using asp/sql - I just need to know if they can be converted and pulled out as I would prefer to avoid creating multiple tables.

my code

<%
'DISPLAY PRICES
DIM objConn19and20
Set objConn19and20 = MYCONNGOESHERE

DIM FLIGHTROUTES19and20
FLIGHTROUTES19and20 = "SELECT * FROM MYTABLENAMEGOESHERE

DIM objRS19and20
Set objRS19and20 = MYCONNGOESHERE

If objRS19and20.Eof Then 
'This checks to see if we are at end of file (eof) for the returned recordset
'if we are there were no records we tell them that
Response.write "<div align='center'>Sorry there are no current records.</div>" 
Else 

Response.Write "<table align=""center"" id=""flightspecialscontainer"" cellpadding=""0"" cellspacing=""0"">"
Response.Write "<tr><td>"

'start a loop that will go until the eof
Do while not objRS19and20.Eof 

'if there were records lets do something with them
'write some table setup
Response.Write "<table align=""center"" cellpadding=""0"" cellspacing=""0"" id=""flightspecialroutetable"">"
Response.Write "<tr><td>" & objRS19and20("Anchor_Tag") & vbCrLf & "<fieldset class=""fieldsetspecialpricebox"">" & vbCrLf
Response.Write "<legend>" & vbCrLf & "<span class=""specialtitlesmall"">" & objRS19and20("Flight_Route") & "</span>" & vbCrLf & "</legend>" & vbCrLf
Response.Write "<table align=center cellpadding=0 cellspacing=0 class=""specialpricetable"">" & vbCrLf
Response.Write "<tr>" & vbCrLf & "<td class=""priceheadertd"">Price</td>" & vbCrLf
Response.Write "<td class=""dateheadertd"">Outbound Date/s</td></tr>" & vbCrLf
Response.Write "<tr><td colspan=2 height=10>&nbsp;</td></tr>" & vbCrLf

'write your table content
Response.Write "<tr>" & vbCrLf & "<td class=""pricetd"">&pound;" & objRS19and20("Price_Band_1") & "</td>" & vbCrLf
Response.Write "<td class=""datetd""><span class=""monthspan"">" & objRS19and20("Month_Band_1") & "&nbsp;</span>" & objRS19and20("Date_Band_1") & "</td>" & vbCrLf & "</tr>" & vbCrLf
Response.Write "<tr>" & vbCrLf & "<td class=""pricetd"">&pound;" & objRS19and20("Price_Band_2") & "</td>" & vbCrLf
Response.Write "<td class=""datetd""><span class=""monthspan"">" & objRS19and20("Month_Band_2") & "&nbsp;</span>" & objRS19and20("Date_Band_2") & "</td>" & vbCrLf & "</tr>" & vbCrLf
Response.Write "<tr>" & vbCrLf & "<td class=""pricetd"">&pound;" & objRS19and20("Price_Band_3") & "</td>" & vbCrLf
Response.Write "<td class=""datetd""><span class=""monthspan"">" & objRS19and20("Month_Band_3") & "&nbsp;</span>" & objRS19and20("Date_Band_3") & "</td>" & vbCrLf & "</tr>" & vbCrLf
Response.Write "<tr>" & vbCrLf & "<td class=""pricetd"">&pound;" & objRS19and20("Price_Band_4") & "</td>" & vbCrLf
Response.Write "<td class=""datetd""><span class=""monthspan"">" & objRS19and20("Month_Band_4") & "&nbsp;</span>" & objRS19and20("Date_Band_4") & "</td>" & vbCrLf & "</tr>" & vbCrLf

'end table setup
Response.Write "<td colspan=""2"" class=""specialbookbuttontd""><a class=""button"" href=""#BackToTop"" onClick=""change_booking(" & objRS19and20("Book_Button") & ")"">BOOK NOW !</a></td>"
Response.Write "</tr>" & vbCrLf & "</table>" & vbCrLf & "</fieldset>"  & vbCrLf
Response.Write "</td></tr></table>" & vbCrLf & vbCrLf

'move to the next record in the recordset
objRS19and20.movenext

Loop 

end if 

'close out your table
Response.Write "</td>" & vbCrLf & "</tr>" & vbCrLf & "</table>" & vbCrLf

objRS19and20.Close
Set objRS19and20 = Nothing
objConn19and20.Close
Set objConn19and20 = Nothing
%>

thanks in advance,
Omar.

[nbiswas]

Hi, you need to write a function either at your backend application or in your frontend application that will convert the INR to EURO.

e.g. 1 Indian rupee = 0.0164715581 Euros (As per 26th March, 2010).

Since we know that, the denomination does not always remain constant, so according to me kindly find a web service and consume that from your client application. So that you don't have to worry about the exact rate when there will be a fluctuation in denomination.

I found some for you . Have a look

a) CurrencyConvert or

b) Noon Foreign Exchange Rates Web Service

Hope this will help you

[omar999]

hey nbiswas

thanks for your reply. i understand what you are saying although i think i need to focus on how to convert the gbp to euro from the front end side for now.

a backend web services integration can be step 2 although i agree with an automated web services feeding the latest currency conversion rate : ) thank you for providing the 2 examples of web services/currency converters but I need to speak to my superiors regarding their sources of currency exchange but thats step 2.

could you advise how I could perform a simple conversion on the front end from the sql statement please?

my table is something like this
ID.....Route... ............... ......Month.... ..Date_Band_1.. .....Price_Band _1
1..........Gatw ick to Toronto.....Mar ch.......13,14. ............... ......449

thanks in advance
regards
Omar.

[nbiswas]

We know that, 1 British pound (GBP)= 1.11541632 Euros (March 26th, 2010)

So the select query will be

select Id, Route,Month,Dat e,Band_1, EuroPrice = Cast( Price_Band_1 * 1.11541632 as Varchar(100)) + space(1) + 'Euros' from tblInfo

[omar999]

thanks - I've got this error 'Arithmetic overflow error converting varchar to data type numeric.' when I used

select ID, Price_Band_3 = Cast( Price_Band_3 * 1.11541632 as Varchar(50)) from TABLENAME WHERE ID='1'

although this works

select ID, Price_Band_3 = Cast( Price_Band_3 * 2 as int) from TABLENAME WHERE ID='1'

not sure why im getting an error as Price_Band_3 = 109 on ID=1 on my table?

[omar999]

i still have this error .. can anyone help please?