Solved

Using a RS.GetString to then get a value from a lookup table

Posted on 2008-06-24
11
524 Views
Last Modified: 2012-06-21
Ok i have a previous question: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_23508088.html

which was answered correctly and did the first step of what i need to do, however now though i need to take it another step...

on the code example below there is a line which says: TextStream.WriteLine RS.GetString(,,",", vbcrlf, "")

Now this works fine and outputs into the CSV file the correct values into each field however fields such as intCategoryID and intSubCategoryID etc are index ID fields which lookup the actual value from another table.  Is it possible to now change the output that get written into a csv/tab delimited file so that it looks up the correct value from the relevant table and now just output an ID number?

For example instead of the output being:
1001, PARTNO, 1, 2
it would be
1001, PARTNO, MYCATEGORY, MYSUBCATEGORY

Thanks,
Carl
'//RETRIVES THE FIELDS SELECTED FROM THE MULTI SELECT BOX (Step1)
strSelected = Request.Form("SelectedFields")
 
'//CREATES THE SQL STATEMENT TO RETRIVE THE CORRECT FIELDS FROM THE DB
strSQL = "SELECT " & strSelected & " FROM tblProducts WHERE intActiveProduct = 1"
 
'//REPLACES ALL OF THE FIELD NAMES WITH USER FRIENDLY FIELD NAMES TO GIVE ME A STRING
'//FOR THE CSV FILE HEADER LINE
strHeader = Replace(strSelected, "intProductID", "ProductID")
strHeader = Replace(strHeader, "txtProductCode", "ProductCode")
strHeader = Replace(strHeader, "intCategoryID", "Category")
strHeader = Replace(strHeader, "intSubCategoryID", "SubCategory")
strHeader = Replace(strHeader, "intSubCategory1ID", "SecondaryCategory1")
strHeader = Replace(strHeader, "intSubCategory2ID", "SecondaryCategory2")
strHeader = Replace(strHeader, "intCategoryFeature", "CategoryFeature")
strHeader = Replace(strHeader, "intCostPrice", "CostPrice")
strHeader = Replace(strHeader, "intRetailPrice", "RetailPrice")
strHeader = Replace(strHeader, "txtProductName", "ProductName")
strHeader = Replace(strHeader, "intManufacturer", "Manufacturer")
strHeader = Replace(strHeader, "txtManufacturerPartNumber", "ManufacturerPartNo")
strHeader = Replace(strHeader, "txtModelNumber", "ModelNumber")
strHeader = Replace(strHeader, "intWeight", "Weight")
strHeader = Replace(strHeader, "txtProductDescription", "Description")
strHeader = Replace(strHeader, "intStockOnHand", "StockOnHand")
strHeader = Replace(strHeader, "intMinimumStockLevel", "MinStockLevel")
strHeader = Replace(strHeader, "intActiveProduct", "ActiveProduct")
strHeader = Replace(strHeader, "intWebProduct", "WebProduct")
strHeader = Replace(strHeader, "intFeaturedProduct", "FeaturedProduct")
strHeader = Replace(strHeader, "txtShortDescription", "ShortDescription")
strHeader = Replace(strHeader, "intBestSeller", "BestSeller")
strHeader = Replace(strHeader, "txtBarCode", "BarCode")
strHeader = Replace(strHeader, "intVatCode", "VatRate")
strHeader = Replace(strHeader, "intRRP", "RRP")
strHeader = Replace(strHeader, "txtSeoTitle", "SEOTitle")
strHeader = Replace(strHeader, "txtMetaKeywords", "SEOKeywords")
strHeader = Replace(strHeader, "txtMetaDescription", "SEODescription")
strHeader = Replace(strHeader, "txtPackSize", "PackSize")
strHeader = Replace(strHeader, "txtMemo", "Memo")
 
 
'//REPLACES ALL OF THE COMMAS FOR THE TABBED HEADER LINE
strTabbedHeader = Replace(strHeader, ",", chr(9))
 
'//IF THE FILETYPE IS TAB DELIMITED THEN RUN THIS SCRIPT
IF intFileType = 1 THEN
	txtFileName = txtFileName & ".txt"
	
		Set fso = Server.CreateObject("Scripting.FileSystemObject")
		path = Server.MapPath(strDownloadCatalogueWebPath)
		Set fldr = fso.GetFolder(path)
		Set TextStream = fldr.CreateTextFile(txtFileName)
			Set RS = Server.CreateObject("ADODB.Recordset")
			RS.Open SQL,Connection,3,3
			TextStream.WriteLine strTabbedHeader
			TextStream.WriteLine RS.GetString(,,chr(9),vbCrlf,"")
			RS.Close
			Set RS = Nothing
		TextStream.Close
'//ELSE CREATE THE CSV FILE TYPE
ELSE
	txtFileName = txtFileName & ".csv"
	
		Set fso = Server.CreateObject("Scripting.FileSystemObject")
		path = Server.MapPath(strDownloadCatalogueWebPath)
		Set fldr = fso.GetFolder(path)
		Set TextStream = fldr.CreateTextFile(txtFileName)
			Set RS = Server.CreateObject("ADODB.Recordset")
			RS.Open strSQL,Connection,3,3
			TextStream.WriteLine strHeader
			TextStream.WriteLine RS.GetString(,,",", vbcrlf, "")
			RS.Close
			Set RS = Nothing
		TextStream.Close
END IF

Open in new window

0
Comment
Question by:net-workx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
11 Comments
 
LVL 22

Expert Comment

by:neeraj523
ID: 21854421
yes.. this is easily achieveable.. but you need to tell the table strcuture of your category tables to pull the relevant category names from it..

then you need to write a join query and pull all needed info and print as desired..
0
 

Author Comment

by:net-workx
ID: 21899078
hi im still strugging a bit with this....

i havnt got a clue where to start with the line:

TextStream.WriteLine RS.GetString(,,",", VbCrLf, "")

from in there do i have to create a connection to the other table  (tblProductCategories) to retreive the txtCategoryName value?

Thanks,
Carl
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 21922537
Right now your variable
  strSelected
probably looks something like:
   txtProductCode, intCategoryID, txtShortDescription,  (etc.)
and that gets incorporated into the SELECT string that obtains the data.  Your SELECT statement probably looks something like:  

    SELECT txtProductCode, intCategoryID, txtShortDescription
    FROM tblProducts WHERE intActiveProduct = 1

 If you want ot replace, for instance, intCategoryID with something from a lookup table (e.g., "Red Widgets") you need a more complicated (compound) SELECT statement.  For instance:

    "SELECT " &
       "txtProductCode, " &
        (SELECT sCatagoryName FROM CatagoryNameTbl WHERE nID=" & intCategoryID & ")," &
        "txtShortDescription " &
    "FROM tblProducts WHERE intActiveProduct = 1"

Which as one line will look like
    SELECT txtProductCode, (SELECT sCatagoryName FROM CatagoryNameTbl WHERE nID=77), txtShortDescription FROM tblProducts WHERE intActiveProduct = 1
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:net-workx
ID: 21958770
Excellent i think that makes sense, I shall test tommorow and let you know how i get on with the compound select statement.
0
 

Author Comment

by:net-workx
ID: 21964513
Ok the problem i have have run into is that the the strSelected string is automatically created by using the users input.. which means that im unable to change the strSeleted before it have been included in the code.

I have changed the code so be:

strSQL = Replace(strHeader, "intCategoryID", (SELECT txtCategoryName FROM tblProductCategories WHERE ID = " & intCategoryID)

but get syntax error at the firts bracket before the SELECT


0
 
LVL 49

Expert Comment

by:DanRollins
ID: 21968538
You forgot to enter the leading double-quote character used to delineate the start of a string.
0
 
LVL 49

Accepted Solution

by:
DanRollins earned 500 total points
ID: 21968667
>> the strSelected string is automatically created by using the users input..

BTW, you should be very careful about allowing user input to be made into an SQL statement.  It opens the door to an malicious exploit...  For instance, if I entered my last name as:
   Rollins";DELETE FROM Customer;
then an update command might look like:
    UPDATE MyTbl Set LastName="Rollins";DELETE FROM Customer;
which would wipe out the entire Customer table.
0
 

Author Comment

by:net-workx
ID: 21974870
sorry i missed that! change it now to:

strSQL = Replace(strHeader, "intCategoryID", ("SELECT txtCategoryName FROM tblProductCategories WHERE ID = " & intCategoryID))

When running i now get an error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e0c'
Command text was not set for the command object.

its on line 124 which is:

RS.Open strSQL,Connection,3,3

Its turning out to be harder to do than i first thought!
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 21977740
The leading and trailing parentheses () are part of the secondary SELECT statement.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.

734 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