Can u help me??

I have written code in ASP which is too big.Actually i have to code samethings many times.Anyone can simplifie this code using Array Or Anything else??

     ''''''''''''''''''''''''''''''AUS Value from Setting Table''''''''''''''''''''''''''''''
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='AUS0_1INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         AUS0_1INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='AUS0_1USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         AUS0_1USD = rs.FIELDS("value")        
    end if
    rs.close
    
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='AUS1_5INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         AUS1_5INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='AUS1_5USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         AUS1_5USD = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='AUS5_INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         AUS5_INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='AUS5_USD'))"
    rs.Open qry,conn

    if not rs.EOF then
         AUS5_USD = rs.FIELDS("value")        
    end if
    rs.close

    '''''''''''''''''''''''''''''''''''''''''''CANADA value from Setting Table''''''''''''''''''''''''''''''''''''''''
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='CANADA0_1INR')) "
    rs.Open qry,conn

    if not rs.EOF then
         CANADA0_1INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='CANADA0_1USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         CANADA0_1USD = rs.FIELDS("value")        
    end if
    rs.close
    
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='CANADA1_5INR')) "
    rs.Open qry,conn

    if not rs.EOF then
         CANADA1_5INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='CANADA1_5USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         CANADA1_5USD = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='CANADA5_INR')) "
    rs.Open qry,conn

    if not rs.EOF then
         CANADA5_INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='CANADA5_USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         CANADA5_USD = rs.FIELDS("value")        
    end if
    rs.close
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''UAE value from Setting Table'''''''''''''''''''''''''''''''''''''''''    
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UAE0_1INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         UAE0_1INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UAE0_1USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         UAE0_1USD = rs.FIELDS("value")        
    end if
    rs.close
    
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UAE1_5INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         UAE1_5INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UAE1_5USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         UAE1_5USD = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UAE5_INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         UAE5_INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UAE5_USD'))"
    rs.Open qry,conn

    if not rs.EOF then
         UAE5_USD = rs.FIELDS("value")        
    end if
    rs.close

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''UK value from Settings''''''''''''''''''''''''''''''''''''''''''
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UK0_1INR')) "
    rs.Open qry,conn

    if not rs.EOF then
         UK0_1INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UK0_1USD'))"
    rs.Open qry,conn

    if not rs.EOF then
         UK0_1USD = rs.FIELDS("value")        
    end if
    rs.close
    
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UK1_5INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         UK1_5INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UK1_5USD'))"
    rs.Open qry,conn

    if not rs.EOF then
         UK1_5USD = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UK5_INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         UK5_INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='UK5_USD'))"
    rs.Open qry,conn

    if not rs.EOF then
         UK5_USD = rs.FIELDS("value")        
    end if
    rs.close

    '''''''''''''''''''''''''''''''''USA value from Settings Table''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='US0_1INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         US0_1INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='US0_1USD'))"
    rs.Open qry,conn

    if not rs.EOF then
         US0_1USD = rs.FIELDS("value")        
    end if
    rs.close
    
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='US1_5INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         US1_5INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='US1_5USD'))"
    rs.Open qry,conn

    if not rs.EOF then
         US1_5USD = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='US5_INR'))"
    rs.Open qry,conn

    if not rs.EOF then
         US5_INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='US5_USD'))"
    rs.Open qry,conn

    if not rs.EOF then
         US5_USD = rs.FIELDS("value")        
    end if
    rs.close
    
    '''''''''''''''''''''''''''''''''OTHER value from Settings Table''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='OTHER0_1INR')) "
    rs.Open qry,conn

    if not rs.EOF then
         OTHER0_1INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='OTHER0_1USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         OTHER0_1USD = rs.FIELDS("value")        
    end if
    rs.close
    
    qry = "SELECT  `value` FROM settings WHERE (((`name`)='OTHER1_5INR')) "
    rs.Open qry,conn

    if not rs.EOF then
         OTHER1_5INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='OTHER1_5USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         OTHER1_5USD = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='OTHER5_INR')) "
    rs.Open qry,conn

    if not rs.EOF then
         OTHER5_INR = rs.FIELDS("value")        
    end if
    rs.close

    qry = "SELECT  `value` FROM settings WHERE (((`name`)='OTHER5_USD')) "
    rs.Open qry,conn

    if not rs.EOF then
         OTHER5_USD = rs.FIELDS("value")        
    end if
    rs.close
 

Code (markup):

 

Try the array code.
My array code may not be the best way, but hope it helps.

--------------
dim ary(2) ' <--- you may add more array here

ary(0)="AUS0_1INR"
ary(1)="AUS0_1USD"
ary(2)="AUS1_5INT"

for i=0 to UBound(ary)

AryX=ary(i)

qry = "SELECT value FROM settings WHERE name='"&AryX&"' "
rs.Open qry,conn

if not rs.EOF then
AryX = rs.FIELDS("value")
end if
rs.close

next

 

i hav not apply yet but thanx for post.

 

Hi Pushkar - you have lots of values called 'name'

You can get all the values from the settings table with

Mysql = "select distinct name from settings"

You can then use this recordset to construct all your queries.

Now, suppose that you only want certain 'name' values.

Mysql = "select distinct name from settings where name in ('OTHER5_INR','OTHER5_USD') "

So this way you just write one list of the values you want.

 

i have applied both of ur ideas but i got better solution in link.ezer.
But thanx to help me..

 

If each of them queries pulls back only 1 record, I would just create 1 query, with all them queries in the sub-queries in the FROM. Just join a pre-set value that will forsurely match

Make sure to use Left Outer Join if they don't exist

For example:

Select AUSO_1INR.value as AUSO_1INR, AUSO_USD.value as AUSO_USD
From
settings left outer join
(SELECT value, 1 as joiner FROM settings WHERE name='AUS0_USD') AUSO_USD on AUSO_USD.joiner = settings.joiner

(SELECT value, 1 as joiner FROM settings WHERE name='......

Just repeat, and all tables will be joined on 1


Using this teqnique you should be able to join all on temp variables and use one query, with returned fields, or whatever