asked on

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

Ok i have a previous question: https://www.experts-exchange.com/questions/23508088/Using-the-mutiple-selections-from-a-form-to-generate-a-text-file.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

neeraj523

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..

net-workx

ASKER

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

DanRollins

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

net-workx

ASKER

Excellent i think that makes sense, I shall test tommorow and let you know how i get on with the compound select statement.

net-workx

ASKER

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

DanRollins

You forgot to enter the leading double-quote character used to delineate the start of a string.

ASKER CERTIFIED SOLUTION

DanRollins

membership

This solution is only available to members.

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

Start Free Trial

net-workx

ASKER

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!

DanRollins

The leading and trailing parentheses () are part of the secondary SELECT statement.