Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

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

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
net-workx
Asked:
net-workx
  • 4
  • 4
1 Solution
 
neeraj523Commented:
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
 
net-workxAuthor Commented:
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
 
DanRollinsCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
net-workxAuthor Commented:
Excellent i think that makes sense, I shall test tommorow and let you know how i get on with the compound select statement.
0
 
net-workxAuthor Commented:
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
 
DanRollinsCommented:
You forgot to enter the leading double-quote character used to delineate the start of a string.
0
 
DanRollinsCommented:
>> 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
 
net-workxAuthor Commented:
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
 
DanRollinsCommented:
The leading and trailing parentheses () are part of the secondary SELECT statement.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now