Classic ASP: Can't Always Use Full Column Name (MYTABLE.MYCOLUMN) When Working with SQL Results

Just to give a couple of examples, if I query . . .

SELECT SHIPPERTABLE.displayName FROM MYDATABASE AND JOINS

 . . then RESPONSE.WRITE(myRS("SHIPPERTABLE.displayName "))

I get an error saying item (column) cannot be found but this

RESPONSE.WRITE(myRS("displayName"))

Works just fine

However if I query . . .

SELECT SHIPPERTABLE.displayName, CONSIGNEETABLE.displayName  FROM MYDATABASE AND JOINS

 this....

RESPONSE.WRITE(myRS("displayName"))

I get an error saying item (column) cannot be found (because now there is a duplicate, the table prefix is required).

Unfortunately without a bunch of if statements, I don't know how to avoid this.

The database is hugely complex and cannot be changed to have unique columnnames (as sometimes the same table is joined multiple times using a table alias).

Hi Sammy,

Many thanks for your response!

I'm afraid because of the build of the web application I cannot create aliases and if I could, it would involve more if statements to dynamically create the SQL statement - something I'm trying to avoid as there are A LOT of potential duplicate columns (the customer table alone is linked in 4 times in total, multiply that by 10 columns in that table and you see where I'm going...).  The frustrating thing is that I can get the system to work with one of the columns (by omitting the table name) or with more than one (by including the table names) but I never know if the user is going to include them or not (it's a dynamic reporting system).

Running the query in SSMS works fine always - this is a problem with ASP/VBScript (I get no SQL error, the problem occurs when accessing the ASP Recordset).

Hi Sammy,

The entire code is huge but here are the bits that's pertinent.

Firstly, my user can select which columns from a multiple (joined) SQL query that they want to appear in a results table:

      <div id="columnSelector">
			<p>Invoice</p>
      <label><input type="checkbox" name="columnNames" value="ACCOUNTS_invoices.invoiceNo" <%IF INSTR(REQUEST("columnNames"),"ACCOUNTS_invoices.invoiceNo") > 0 THEN RESPONSE.WRITE(" checked=""checked""")%> /> Invoice No</label>
      <label><input type="checkbox" name="columnNames"  value="invoiceDate" <%IF INSTR(REQUEST("columnNames"),"invoiceDate") > 0 THEN RESPONSE.WRITE(" checked=""checked""")%> /> Invoice Date</label>
      <label><input type="checkbox" name="columnNames"  value="invoiceTotal" <%IF INSTR(REQUEST("columnNames"),"invoiceTotal") > 0 THEN RESPONSE.WRITE(" checked=""checked""")%> /> Invoice Total</label>
      <label><input type="checkbox" name="columnNames"  value="CUSTOMER_customers.displayAs" <%IF INSTR(REQUEST("columnNames"),"CUSTOMER_customers.displayAs") > 0 THEN RESPONSE.WRITE(" checked=""checked""")%> /> Invoice Customer</label>
			<p>AWB</p>
      <label><input type="checkbox" name="columnNames"  value="FREIGHT_awbs.awbNo" <%IF INSTR(REQUEST("columnNames"),"FREIGHT_awbs.awbNo") > 0 THEN RESPONSE.WRITE(" checked=""checked""")%>  /> AwbNo</label>
      <label><input type="checkbox" name="columnNames"  value="AWB_shippers.displayAs" <%IF INSTR(REQUEST("columnNames"),"AWB_shippers.displayAs") > 0 THEN RESPONSE.WRITE(" checked=""checked""")%>  /> Shipper Name</label>
      <label><input type="checkbox" name="columnNames"  value="AWB_consignees.displayAs" <%IF INSTR(REQUEST("columnNames"),"AWB_consignees.displayAs") > 0 THEN RESPONSE.WRITE(" checked=""checked""")%>  /> Consignee Name</label>
			<p>AWB</p>
      <label><input type="checkbox" name="columnNames"  value="FREIGHT_awbs.awbNo" <%IF INSTR(REQUEST("columnNames"),"FREIGHT_awbs.awbNo") > 0 THEN RESPONSE.WRITE(" checked=""checked""")%>  /> AwbNo</label>
      </div>

Select allOpen in new window

As these checkboxes have the same name, the column names are returned as a comma delimited list (for example 'AWB_consignees.displayAs', 'AWB_shippers.displayAs').

This is used to build the SQL Query column name list:

sql = "SELECT " & columnNames


tableJoinSQL = " FROM CUSTOMER_customers AS AWB_shippers RIGHT OUTER JOIN CARRIERS_carriers INNER JOIN FREIGHT_awbs ON CARRIERS_carriers.carrierID = FREIGHT_awbs.carrierID ON AWB_shippers.customerID = FREIGHT_awbs.shipperID LEFT OUTER JOIN CUSTOMER_customers AS AWB_consignees ON FREIGHT_awbs.consigneeID = AWB_consignees.customerID RIGHT OUTER JOIN ACCOUNTS_invoiceLines INNER JOIN ACCOUNTS_invoices INNER JOIN CUSTOMER_customers ON ACCOUNTS_invoices.customerID = CUSTOMER_customers.customerID ON ACCOUNTS_invoiceLines.invoiceNo = ACCOUNTS_invoices.invoiceNo ON FREIGHT_awbs.awbNo = ACCOUNTS_invoices.awbNo "
SESSION("SQL") = sql & tableJoinSQL

Select allOpen in new window

The comma delimited list is then converted to an ARRAY (using 'SPLIT') and then used to build the output data table (the code below loops through the recordset and then the array within the recordset to dynamically generated rows and columns):

	pagedListRS.AbsolutePage = cp
	WHILE NOT pagedListRS.EOF AND recordsPerPage > 0
%>
    </tr>
	<%
	RESPONSE.WRITE("<tr")
	IF highLightRow THEN
		highLightRow = FALSE
		RESPONSE.WRITE(" class=""rwBck""")
	ELSE
		highLightRow = TRUE
	END IF
	RESPONSE.WRITE(">")
		FOR EACH columnName IN columnNames
%>
            <td><%
						IF columnName = "ACCOUNTS_invoices.invoiceNo" THEN
							RESPONSE.WRITE(pagedListRS("invoiceNo"))
						ELSEIF columnName = "FREIGHT_awbs.awbNo" THEN
							RESPONSE.WRITE(pagedListRS("awbNo"))
						ELSE						
							RESPONSE.WRITE(pagedListRS(columnName))
						END IF
						%></td>
<%
		NEXT
		recordsPerPage = recordsPerPage - 1
		pagedListRS.MoveNext
	WEND

Select allOpen in new window

So as you can see, I can easily created aliases for each one within the SQL because the checkbox would have to contain "SHIPPERS_customers.displayAs AS shippersDisplayAs".  This would make the SQL bit work fine but then when I try to dynamically access the recordset column using the same checkbox value, I'd effectively be doing:

myRecordsetRS("SHIPPERS_customers.displayAs AS shippersDisplayAs") instead of
myRecordsetRS("SHIPPERS_customers.displayAs") or myRecordsetRS("displayAs").

Now the whole problem I'm having is I'd just like to be able to use myRecordsetRS("SHIPPERS_customers.displayAs") for everything but if I specify the table name when accessing the recordset column and there is only one 'displayAs' column, I get an error (presumably because there is no need to specify the column as there isn't a column with the same name).

Acperkins,

Thanks for the response, I figured I'm going to have to have my checkboxes pass two values, delimited by something other than a comma, one being the table.columnName and the other being the alias.  Not as clean but it's what I've got to do.  I was hoping there would be a simple way to simply always be able to use the full column name (tableName.ColumnName) but that's obviously not the case (seems weird to me).

You say "you cannot refer to the table names in your ASP code, there is no concept of "table" in a recordset", I understand that a recordset is simply that; one set of results - it was more of an expression in that two columns with the same name would be differentiated by their table name when retrieving row values from said recordset (if you don't use an alias), e.g. tableOne.CustomerName and tableTwo.customerName - what this whole question is about really.

The best and simplest way to do this is to explicitly name each of your columns so that you can refer to them in the ASP script accurately every time.

So this example query that you posted earlier:

SELECT SHIPPERTABLE.displayName, CONSIGNEETABLE.displayName  FROM MYDATABASE AND JOINS...

would change to this:

SELECT SHIPPERTABLE.displayName AS ShipperName, CONSIGNEETABLE.displayName As ConsigneeName  FROM MYDATABASE AND JOINS...

Then reference your columns in the code as follows:

RESPONSE.WRITE(myRS("ShipperName"))
RESPONSE.WRITE(myRS("ConsigneeName"))

Easy, peasy, lemon squeezy :)

Hey WorthyKing,

Thanks for the response - this is simply apply aliases as mentioned above, it is the solution but in this instance is far from easy (see my last post) but is the only solution I can see!

Bob

I've requested that this question be deleted for the following reason:

Answered my question myself.

I wish to cancel my deletion request, I do not want to destabilize the world of EE.

The End.