ASP/VBScript SQL wierdness.

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

ASP/VBScript SQL wierdness.

Logged in as: Guest

arrSession:exec spGetSession 2,2,33958

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> ASP/VBScript SQL wierdness.
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

ASP/VBScript SQL wierdness. - 4/26/2006 2:52:07 AM

feyrer

Posts: 6
Score: 0
Joined: 4/18/2006
Status: offline

This line of code compiles just fine in SQL Query but when I put it into the code as a Recordset it's erroring out, saying office ID (LO_OFFICE.ID, here as E.ID) doesn't exist. Any idea why? A note that import_TITLETRANSFORM M does have an ID field as well,but it isn't queried for the result set. Is that the source of the problem? I remember you saying something about this..thanks.
Here's the query: strSQLCNORS1 = "SELECT E.ID, E.DTYPE, E.DNUMBER, E.MCDFIPS, E.COFIPS, M.NEW_TITLE2, E.NextElection, M.ELECTED, M.ASSISTANT, E.ADR, E.ADR2, E.CITY, E.STATE, E.ZIP, E.WPHONE, E.FAX, E.WEMAIL, E.ATC_ZONE, M.WADR FROM LO_OFFICE E INNER JOIN import_TITLETRANSFORM M ON E.DNAME = M.OLD_TITLE WHERE(M.MULTI_TITLE = '1')" And the error: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'ID'. /local_test/newsubroutine.asp, line 7 Any suggestions would be extremely helpful -- Christopher Feyrer

Post #: 1

RE: ASP/VBScript SQL wierdness. - 4/26/2006 3:02:36 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

Is this your actual code? If so then you need an AS clause.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to feyrer)

Post #: 2

RE: ASP/VBScript SQL wierdness. - 4/26/2006 3:05:50 AM

feyrer

Posts: 6
Score: 0
Joined: 4/18/2006
Status: offline

strSQLCNORS1 = "SELECT E.ID as EID, M.ID AS MID, E.DTYPE, E.DNUMBER, E.MCDFIPS, E.COFIPS, M.NEW_TITLE2, E.NextElection, M.ELECTED, M.ASSISTANT, E.ADR, E.ADR2, E.CITY, E.STATE, E.ZIP, E.WPHONE, E.FAX, E.WEMAIL, E.ATC_ZONE, M.WADR FROM LO_OFFICE E INNER JOIN import_TITLETRANSFORM M ON E.DNAME = M.OLD_TITLE WHERE(M.MULTI_TITLE = '1')"

Same error, do I need AS CLAUSES for everything? I assigned M.ID as MID just to be sure..

(in reply to ebgreen)

Post #: 3

RE: ASP/VBScript SQL wierdness. - 4/26/2006 3:09:55 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

What table is this coming form: E.ID? How does the query know?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to feyrer)

Post #: 4

RE: ASP/VBScript SQL wierdness. - 4/26/2006 3:12:45 AM

feyrer

Posts: 6
Score: 0
Joined: 4/18/2006
Status: offline

it comes from the table LO_OFFICE, which is aliased in the "FROM" portion of the statement. It compiles fine on SQL server directly, just not in this code for some reason!

(in reply to ebgreen)

Post #: 5

RE: ASP/VBScript SQL wierdness. - 4/26/2006 3:16:39 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

So _TITLETRANSFORM is a stored procedure?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to feyrer)

Post #: 6

RE: ASP/VBScript SQL wierdness. - 4/26/2006 3:19:28 AM

feyrer

Posts: 6
Score: 0
Joined: 4/18/2006
Status: offline

IMPORT_TITLETRANSFORM (aliased as M) is a table linked to LO_OFFICE (aliased as E) on E.DNAME = M.OLD_TITLE

This is a big select statement. no transformations are actually taking place. All it's erroring out on is selecting ID for LO_OFFICE.

(in reply to ebgreen)

Post #: 7

RE: ASP/VBScript SQL wierdness. - 5/1/2006 8:55:55 AM

feyrer

Posts: 6
Score: 0
Joined: 4/18/2006
Status: offline

The previous query is resolved, it turns out it was a field in the actual database being misnamed. However, now I'm having another VBscript issue along the same lines..

The following SQL generates one record with a value of 1 for EDATEID when executed directly to the SQL server:
SELECT TOP 1 N.eDate AS EDATEID FROM LO_ELECTION N INNER JOIN LO_ELECTED D ON N.electedID = D.ID INNER JOIN LO_OFFICE E ON D.OfficeID = E.ID WHERE E.ID = 149 ORDER BY N.Last_update DESC

However, when submitted using the following code:
set CNORS2 = CreateObject("ADODB.Recordset")
strSQLCNO2RS = "SELECT TOP 1 N.eDate AS EDATEID FROM LO_ELECTION N INNER JOIN LO_ELECTED D ON N.electedID = D.ID INNER JOIN LO_OFFICE E ON D.OfficeID = E.ID WHERE E.ID = " & intMULTIOffice_ID & " ORDER BY N.Last_update DESC"
response.write " " & strSQLCNO2RS
CNORS2.Open strinsertstr, sConnect
response.write " " & CNORS2.Fields.Count & " Fields "
response.write" " & CNORS2.Fields.Item(1) & " is item 1 " *** This is line 61
response.write CNORS2("EDATEID")
intEDateID = CNORS2("EDATEID")

it returns:

0 Fields

ADODB.Fields error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/local_test/newsubroutine.asp, line 61

Ideas??

(in reply to feyrer)

Post #: 8

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> ASP/VBScript SQL wierdness.

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts