Solved

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

Posted on 2008-06-24
11
516 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now