error '80040e14' - Incorrect syntax near the keyword 'select'

[psappenfield]

Hello,

I am having a problem with an SQL query string.

strQry="select top 1 AvailableDayOfWeek from myMeetingAvailableTimeSlots WHERE ManagerName='sbaker'"

objRS.Open strQry,objConn,adOpenStatic,adLockOptimistic,adCmdTable

I have run it through SQL Query Analyzer, and there were no errors.

I am running the query on a SQL Server 7 database, and I had to make a name change on the table.

Does anyone know how to fix this?

Thanks in advance...

 

[vbkris]

i dont see any error...

Known is handfull, Unknown is worldfull

 

[psappenfield]

The query was running just fine until I changed a column name in the SQL table.

Unfortunately, ASP thinks there is an error. I am getting this error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'select'. 

/iothome/Accountability/SelectTime.asp, line 84

Does anyone know of an issue with column name changes in SQL causing ASP errors?

 

[vbkris]

what column did u change and to what did u change it to???

Known is handfull, Unknown is worldfull

 

[psappenfield]

I changed the MangerName field to ManagerName.

Originally the query did not have this ManagerName constraint, since I was just testing my connection to the table. It worked fine at that point.

When I realized that I had misspelled the field name, I changed it, and then that error started giving me problems.

Any ideas?

 

[palagrim]

In my rather limited experience, this would suggest that your objConn object is invalid. So what you're passing to the SQL server is actually:

SELECT ,, top 1 AvailableDayOfWeek from myMeetingAvailableTimeSlots WHERE ManagerName='sbaker'

Have you tried setting the active connection of the objRS object?

So:

Dim objConn, objRS, strQry

Set objConn = server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=sqloledb;" & _
                   "Data Source=server,port;" & _
                   "Network Library=DBMSSOCN;" & _
                   "Initial Catalog=whatever;" & _
                   "User ID=username;" & _
                   "Password=password;"
objConn.open

Set objRS = server.CreateObject("ADODB.Recordset")
objRS.activeconnection = objConn

strQry = "select top 1 AvailableDayOfWeek from myMeetingAvailableTimeSlots WHERE ManagerName='sbaker'"

objRS.open strQRY,adOpenStatic,adLockOptimistic,adCmdTable

Set objConn = nothing

Hope this was of some help?

- Craig

 

[palagrim]

Just noticed the other replies, I've personally never had any issue with renaming column names and then editing the query string.

The error is being picked up by SELECT which would suggest the objConn is invalid...

... but then I have been wrong before...

- Craig

 

[fischadler]

Is "myMeetingAvailableTimeSlots" a Table or a predefined Query?

-Fischadler

 

[psappenfield]

I have the connection object at the top of the page...

strConnect1="DRIVER={SQL Server};" &_
   "SERVER=myserver;DATABASE=mydatabase;" &_
   "Trusted_Connection=yes;"

set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnect1
set objRS = Server.CreateObject("ADODB.Recordset")
set objRS.ActiveConnection=objConn

The connection is not giving me problems, because I can query on other tables without any issues. I only get the error from the table where the name change occured.

Thanks for the suggestion though, since I had not tested that yet.

Any other ideas?

 

[psappenfield]

It is a table.

 

[vbkris]

does this sql execute correctly in ur query analyser???
a dumb question but are u connected to the correct DB???

Known is handfull, Unknown is worldfull

 

[psappenfield]

It does work in Query Analyzer, and I am connected to the right database.

I have had issues like this before with Sql Server 7 and column insert changes in DTS, but that was a column order issue. This time the column order has not been altered, just the name.

Is there anyway to get more information out of the error message, so I can determine where exactly the problem is occurring?

Thanks...

 

[Sheco]

For debugging purposes, you might want to try adding a little Response.Write strQry immediately before you execute the query... just to make sure that the query that you are running is what you think it is.

 

[DotNetGnat]

are you sure that there were no spaces while changing the field name...

try using square braces...

WHERE [ManagerName]='sbaker'

-DNG

 

[psappenfield]

Thank everyone for your help... It was the open connection string...

I was using adCmdTable instead of adCmdText.