Generate variables from SQL recordset using classic ASP/VBScript

ASKER

pceiba, thanks that was definitely part of the solution. Use of the ISNULL very elegant, would have saved me an hour and provides just one result, AgentWord along with WordID.

But also now need the ASP coding on defining variables in ASP/VBScript, so that for example:-
<%
word1 = rsTran.Fields.Item("AgentWord").Value %>
etc for each record of WordID and AgentWord in the recordset
This seems very hard, as not sure how arrays can work here and there do not seem to be other alternative routes for dynamic variables.

What is a repeat region?

I'm not exactly sure what you're trying to do. In any case, I would advise using an array to avoid the need to generate variables on the fly (which isn't trivial). Have a look at GetRows:

http://www.w3schools.com/ado/met_rs_getrows.asp
http://www.aspdev.org/asp/asp-getrows/

While ASP will let you defined variables on the fly this is bad practice, ergo Option Explicit which requires all variables to be Dimensioned before use. The only way I know to create (Dimension) variables within executing code is with the Redim which of course applies to arrays.

Arrays are probably your best bet. You could have one array to hold names and one to hold values, and write some code to associate the two.

Another option, while it has some memory overhead is using the Scripting Directory.

ASKER

Thanks, perhaps I've gone about this the wrong way, so best to indicate what I was trying to do. All in SQL 2000.

The problem: We have some webpages and want the label text to appear in different languages according to the defined business client (defined by query string id2=xxxx). Rather than have many versions of the same page for different languages, we have one with parametric text, eg <%=word67%><%=word5%>. These pages have already been coded, and work with Russian and English. Approx 200 words and phrases.
Solution 1: We created table1 for short phrases up to nvarchar 30, table2 for longer phrases to 350. Each table has fields id2, language, word1, word2 etc. Works fine, but 2 big limitations: hard to get words in English alphabetic order, and max row size quickly reached. Very clunky. But is working fine.
Solution 2: This what we now trying. Just one table3, with id2, wordID, word, phrase. So we do a join between business clients with same wordID to get the translations. Much more elegant, easy to add new words to translate etc. BUT the big problem is the last stage, actually getting the data out to use in the webpages! This is the problem outlined above.
Should I stick with 1, perhaps better designed, or attempt 2?

Don't use DB for that. At least not from the front-end. Either use ASP's Dictionary object (http://www.w3schools.com/asp/asp_ref_dictionary.asp), or use XML files for each language and use XPath to get a translation for a word.

Pavel Celba

Interesting. I am not ASP expert but the Dictionary object seems to be a good choice for some smaller and simpler projects. XML files look better but you have to manipulate with the whole file even when you need some minor change and if I understand it well then orientation in very big XML file is not as easy and you should rather place it into some database.

The dictionary stored in a database should be the target for large projects.

But please remember, I am not an expert...

You are wrong. Dictionary is a much lighter solution than XML, and a DB for this purpose is an overkill. This needs to be stored on-memory (cached), hence a DB is not an option. XML files, while loaded, consume a lot more than a Dictionary.

ASKER

Thanks for the comments, definitely sounds like I went for overkill with DB. I looked up ASP Dictionary. As it is based on pairs, ie key/item, how would we work this for multiple languages? Seems ideal for 2 languages. Is it still easy to use if the same English key, eg "yacht", is used in several pairs eg English/Russian, English/Spanish, English/German?

Either have an object of a class containing the words in different languages (or a simple array where each cell represents a language based on a language ID you'll set), or have several Dictionary objects. That's all implementation dependent. I'd advise storing this object in the Application object (as opposed to loading it for each page or storing it per Session). Also, I would highly advise moving to ASP.NET.

ASKER

3 further questions on thinking further:-

1) Would best be to have each language dictionary in a separate ASP file that is selected for server-side inclusion according to the desired language? I am assuming that this works for Cyrillic characters etc.

2) If each directory page has about 150 words/short phrase, and 30 longer 200 - 500 characters, is this still faster for the user than using a database?

ASKER

Sorry, 3rd question missing:-

3) The translated values in other languages are generated by business clients, not us, and entered via form into the DB. If we use the ASP dictionary, is there an alternative to a manual process whereby client sends us a file/form and this is then used to create the dictionary file?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

>>>Rather than have many versions of the same page for different languages, we have one with parametric text, eg <%=word67%><%=word5%>. These pages have already been coded, and work with Russian and English. Approx 200 words and phrases.

So you have one ASP page per language with Const declarations for the words and phrases, exactly like you have type libraries for ADO, CDO, etc.

Russian language page, include the Russian include page, German language page include the German include page.

<%
' English

Const word1 = "cat"
Const word2 = "dog"
...
Const phrase1 = "Click Submit to continue"
%>

Simple, clean and easy to maintain. Low overhead and no database access.

Can also make a master file with all the words in English, to be filled in later by a translater:

<%
Const word1 = "" 'cat
Const word2 = "" 'dog
...
Const phrase1 = "" ' Click Submit to continue
%>

<%
'--------------------------------------------------------------------
' Microsoft ADO
' Copyright (c) 1996-1998 Microsoft Corporation.
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CursorOptionEnum Values ----
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBookmark = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000
Const adFind = &H00080000
Const adSeek = &H00400000
Const adIndex = &H00800000

[snip]

Open in new window

Just loading a Scripting Dictionary would require unnecessary overhead compared to the simple file include. You have no need for the overhead of XML, there is no value in the extra markup, and you don't have to make a trip to the database for a potentially large query you'll need to maintain in memory anyway.

Therefore, this method can not take up more memory than the other suggestions, its already a proven standard method, e.g. the type libraries, and it is trivial to maintain after you make one master page, (which can probably be 90%+ created from your existing database tables.)

True, that overhead exists, but it happens once in the application's lifetime. It would size in memory just a bit more than a file like you suggested will (variants are loaded into memory as well, even if it's more transparent to the dev...), and it will allow for live changes without file editing.

>and it will allow for live changes without file editing.

"Welcome" is going to change meaning during a user's session?

ASKER

I have been trying simple includes files, one for Russian, one for English. Both generated from the DB initially. Each works remarkably well, definitely seems much faster than the DB approach I was using.

BUT I have the problem of how to switch the include file depending on the business client (=agent).

If I put in simple IF...Then, eg
<% If agent = 1045 Then %>

<% Else %>

<% End If %>

Then I hit error problem below from redefining the constant (obviously I need the same constant values for the one page to work with multiple languages):-

Microsoft VBScript compilation error '800a0411'
Name redefined
D:\DOMAINS\etc\../includes/site/incl-english.asp, line 5
Const word1 = "price"
------^

I tried If..Then with Server.Execute, but then all the placeholders were blank.

ASKER

2 further questions:-

1) The problem above goes away if I remove "Const" from the include files. So they become:-
<%
' English
Const word1 = "cat"
Const word2 = "dog" etc

So one solution would be to put all the language definitions in one include file, with if..then defining which applies, and having Dim word1, word2 etc at the top of the first. Best solution?

2) The include files have no header, they simply start <% and end %>. Cannot include <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%> because already in the main page.

Resut is that for Russian I get this Dreamweaver error message and the text is saved as ?????:-
"The document's current encoding cannot correctly save all of the characters within the document. You may want to change to UTF-8 or an encoding that supports the special characters in this documents."

So I added this at the top, but although it worked, it screwed up the formatting:-
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

How to solve this? Thanks

ASKER

Sorry, made stupid mistake at top of last entry. It should start:-

2 further questions:-

1) The problem above goes away if I remove "Const" from the include files. So they become:-
<%
' English
word1 = "cat"
word2 = "dog" etc

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

@rdivilbiss - The sample you provided in http://www.rodsdot.com/asp/testLanguageInclude.asp is much less efficient than the methods I provided above, which perform one heavy task at Application_Start and then stores the ready object in memory. Performing an HTTP GET on each page load, and then parsing it, is definitely more expensive and much less ug

I would agree using include files as you suggested may be better than using a Dictionary object, and kudos for the Excel hack, but if he's unable to resolve the SSI issue he's better off with my solution.

@pld51 - Re. the SSI issue - this is a known issue and I can't remember the best solution for this at the moment. IIRC you may load the required ASP file using FSO and then run eval. Or, you can create different folders for different languages and have an auto tool to copy the main ASP files from your root. That's ugly, hence my suggestions above for using dynamic objects.

Love the Application object. In fact every page on my website uses it.

And I don't disagree it may be beneficial if the SSI issue can't be resolved. I'm all for what ever works best.

FSO - Eval isn't going to be efficient.

ASKER

OK, great, I think thanks to these comments I have now a working solution:-

a) various includes files, eg incl-russian.asp, without any Const declaration
b) a file include-languages.asp that defines which one to use, and starts with Dim definitions of all the variables (treated as variables, since Const redefinition problem not easily solvable), eg
<% Dim word1, word2, etc
If agent = 1045 Then %>

<% Else %>

<% End If %>

c) Although the Excel files could be helpful, and I had sent a similar one off to an agent, I suddenly realized I had wasted so much time on creating 2 database solutions. I took the second, for which an input form had been created, and sent this instead to the agent. Now when his input is complete, I have an ASP page that prints out in HTML
word1 = "xxxxxx"
word2 = "yyyyyy" etc
I copy and paste this into the new incl-languagezzz.asp file. So the database is only used to obtain more efficiently the data from the agent. And looks more professional. The database is NOT used for delivering the translations to the pages themselves, this is done by the static includes files.

If later this gives performance problems, I will try Stilgar's approach which is entirely new to me.

ASKER

Thanks indeed for great help. Sorry, did not really know how best to allocate points, but as I mainly applied method of rdivilbiss, this received most points.