Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Generate variables from SQL recordset using classic ASP/VBScript

Posted on 2010-01-06
29
Medium Priority
?
965 Views
Last Modified: 2013-11-19
I have an SQL recordset rsTran that gives translations of some words. The key fields in a record are WordID, WordOrig, WordTran. Note that WordID is not an index (there is a separate one), and WordTran sometimes is null.

I want to generate variables that can be used in classic ASP webpages for the words from this recordset, but am having difficulty. The variables would be for example <%=word11%> where word11 is defined either equal to WordTran (if not null) or else WordOrig, where the WordID is 11.

How to do this, using a repeat region that expands automatically as more words are added to the recordset?
0
Comment
Question by:pld51
  • 10
  • 8
  • 7
  • +1
27 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 26195563
So, if this question is related to this one: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_25001806.html?cid=748
then you may simply update the query to return original word if the translation is not defined. ISNULL function will help.

If you've changed the original query then you have to post it here and we may work on it. If your problem is in ASP code then post it here also and some ASP expert will help for sure.

SELECT     A1.Word, A1.WebAgentID, ISNULL(A2.Word, A1.Word) AS AgentWord, A2.WebAgentID AS Agent  
  FROM     dbo.tblTranslateNewCopy A1  
  LEFT OUTER JOIN dbo.tblTranslateNewCopy A2 ON A1.WordID = A2.WordID AND A2.WebAgentID = 28 
 WHERE     A1.WebAgentID = 1

Open in new window

0
 

Author Comment

by:pld51
ID: 26195858
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.
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26301572
What is a repeat region?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:_Stilgar_
ID: 26302668
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/
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26310495
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.
0
 

Author Comment

by:pld51
ID: 26316524
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?
0
 
LVL 14

Expert Comment

by:_Stilgar_
ID: 26316690
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.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26317097
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...
0
 
LVL 14

Expert Comment

by:_Stilgar_
ID: 26317178
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.
0
 

Author Comment

by:pld51
ID: 26318197
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?
0
 
LVL 14

Expert Comment

by:_Stilgar_
ID: 26318271
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.
0
 

Author Comment

by:pld51
ID: 26318399
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?
0
 

Author Comment

by:pld51
ID: 26318451
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?
0
 
LVL 14

Assisted Solution

by:_Stilgar_
_Stilgar_ earned 400 total points
ID: 26318468
What I would do is have one dictionary at the application level (stored under Application("langsDict") or under the Server object), where the key is the english word/phrase and the value is an array, each cell of it is the value for another language. If done this way, you keep with one dictionary in one code file.

Since the dictionary is loaded and used in the application scope, it doesn't matter how many words each page uses. It is still faster since accessing a memory object (even large) is still faster than initializing a DB call. Thats a basic memory-performance tradeoff.

A word of warning -- while this is a valid method for .NET, I think I recall a performance warning regarding this. It has been a while, so it does worth checking this out.

You can still have the strings managed from the DB, but its loading will be done by the application on its initialization (see global.asa events). When doing that this way you actually save tons of DB connections (which aren't cheap) per page. DB tables should worry about word length - just use nvarchar(MAX).
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26318863
>>>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

0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26318947
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.)
0
 
LVL 14

Expert Comment

by:_Stilgar_
ID: 26319035
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.
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26319445
>and it will allow for live changes without file editing.

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

Author Comment

by:pld51
ID: 26323326
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 %>
<!--#include file="../includes/site/incl-russian.asp" -->
<% Else %>
<!--#include file="../includes/site/incl-english.asp" -->
<% 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.
0
 

Author Comment

by:pld51
ID: 26323618
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
0
 

Author Comment

by:pld51
ID: 26323641
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
0
 
LVL 29

Accepted Solution

by:
rdivilbiss earned 1600 total points
ID: 26329681
<% If agent = 1045 Then %>
<!--#include file="../includes/site/incl-russian.asp" -->
<% Else %>
<!--#include file="../includes/site/incl-english.asp" -->
<% End If %>

Because <!--#include file="../includes/site/incl-russian.asp" --> is a server side include and occurs before the ASP is parsed.

http://www.rodsdot.com/asp/testLanguageInclude.asp works using the server side XMLHTTP object.  But, the file structure isn't as pretty, but it is smaller.

You can send an Excel file like the attached to a translator.  It only takes a few minutes to convert the translated text into the include file format, which is simply a comma separated list. You copy a simple formula down the column as in the second file, the copy and paste into your web file.

I can probably come up with something better after a little sleep.  Its very late here.

translate.xls
translate2.xls
0
 
LVL 14

Expert Comment

by:_Stilgar_
ID: 26331229
@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.
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26332345
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.

0
 

Author Comment

by:pld51
ID: 26341143
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 %>
<!--#include file="../includes/site/incl-russian.asp" -->
<% Else %>
<!--#include file="../includes/site/incl-english.asp" -->
<% 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.
0
 

Author Closing Comment

by:pld51
ID: 31673650
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.
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26347374
Actually there are conditional SSI statements, but I have not got a working example yet...you insired me to rewite my authentication system using internationalization...so I got distracted.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses
Course of the Month20 days, 21 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question