Error in selection

I use the code below connect to oracle but get the following error.
Any suggestion?


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Oracle][ODBC][Ora]ORA-00904: invalid column name
/db/Default.asp, line 134


objConn = Server.CreateObject("ADODB.Connection");
objConn.Open("DSN=PQMS;Database=PQMS;UID=pquser;PWD=pquser;");
var myRS = Server.CreateObject("ADODB.Recordset");

var distid="20020815-0002";
myRS.Open("SELECT recsubstationkey FROM recorder a, pgdisturbance b, pgevent c, instance d WHERE c.pgdistkey=b.pgdistid and a.recid=d.instlockey and d.instid=c.pginstkey and b.pgdisteventid='"+distid+"'", objConn);
var myVar="";
turbot_yuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try to output the SQL as generated, and run it in SQL*Plus. that will point to the column name that is the problem
RCorfmanCommented:
Check the tables include in the select statement and ensure all are correct.
Not sure what table recsubstationkey is in, but must be in one of the following
recorder, pgdisturbance, pgevent or instance
Also, ensure the rollowing are columns in the right table
recorder - recid
pgdistrubance - pgdistid and pgdisteventid
pgevent - pgdistkey and pginstkey
instance - instlockey and instid

it is either a type or picking a column from a wrong table.
turbot_yuAuthor Commented:
I tried:
var strSQL = "";
strSQL = "SELECT recsubstationkey FROM recorder a, pgdisturbance b, pgevent c, instance d WHERE c.pgdistkey=b.pgdistid and a.recid=d.instlockey and d.instid=c.pginstkey and pgdistid='" + distid + "'";

Response.Write("SQL Query: " & strSQL);

but only give a '0', no other words, any suggeston?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
any better with this:

var strSQL = "";
strSQL = "SELECT recsubstationkey FROM recorder a, pgdisturbance b, pgevent c, instance d WHERE c.pgdistkey=b.pgdistid and a.recid=d.instlockey and d.instid=c.pginstkey and pgdistid='" & distid & "'";
turbot_yuAuthor Commented:
Now I try :

var strSQL = "";
strSQL = "SELECT ";
Response.Write("SQL Query: " &strSQL);

still a '0' there, if I comments '//Response.Write("SQL Query: " &strSQL);'
The '0' will disappear, so it caused it.

Any suggestion?
Guy Hengel [angelIII / a3]Billing EngineerCommented:

var strSQL = "";
strSQL = "SELECT recsubstationkey FROM recorder a, pgdisturbance b, pgevent c, instance d WHERE c.pgdistkey=b.pgdistid and a.recid=d.instlockey and d.instid=c.pginstkey and pgdistid='" + distid + "'";

Response.Write("SQL Query: " + strSQL);

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geotigerCommented:
You need to put the db alias in your select clause since you have defined them in the where clause. I assume the column is from table recorder. If it is not, you can change to the correct one.

var strSQL = "";
strSQL = "SELECT a.recsubstationkey FROM recorder a, pgdisturbance b, pgevent c, instance d WHERE c.pgdistkey=b.pgdistid and a.recid=d.instlockey and d.instid=c.pginstkey and pgdistid='" + distid + "'";
turbot_yuAuthor Commented:
I tried 'a.recsubstationkey', but still got same error:
any suggestion?

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Oracle][ODBC][Ora]ORA-00904: invalid column name
/db/Default.asp, line 138
turbot_yuAuthor Commented:
while select other columns seems not have problem by code, any suggestion?
RCorfmanCommented:
The query seems to have changed from your original of:
SELECT recsubstationkey FROM recorder a, pgdisturbance b, pgevent c, instance d WHERE c.pgdistkey=b.pgdistid and a.recid=d.instlockey and d.instid=c.pginstkey and b.pgdisteventid='"+distid+"'"
to the last posted of:
SELECT a.recsubstationkey FROM recorder a, pgdisturbance b, pgevent c, instance d WHERE c.pgdistkey=b.pgdistid and a.recid=d.instlockey and d.instid=c.pginstkey and pgdistid='" + distid + "'"

They aren't the same....

I think you need to CONFIRM the column names in the tables, that is the most likely problem. Something as simple as you really do have a column mispelled or against the wrong table.

Another possibility, and I ran some tests and didn't receive the same error, it worked ok for me, but still could be an issue.... is that INSTANCE is a keyword in Oracle. You might try placing it in "'s and capitalizing it.
FROM ....... pgevent c, "INSTANCE" d WHERE....
You'll need to 'escape' the double quote around instance. As I said, this didn't cause me a problem, I was able to execute a query with instance as a tablename, but it really is a keyword and potentially this is causing some problem.

More likely is just a mis-spelled column name, or attaching the wrong table to it, like b.pgdistid where it should be c.pgdistid (not necessarily that column, but another). Please, verify all column spellings to be exactly right.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.