Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 3rd, 2004, 11:37 PM
Hez Hez is offline
Registered User
 
Join Date: Aug 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Incorrect syntax near '`'

I used the upsizing wizard in Access to convert a DB to SQL2000. The query in Access was,

SELECT [pLastName]+', '+[pFirstName] AS Name, [pMedicalRecord]
FROM Pinfo
ORDER BY [pLastName], [pFirstName];

It has been converted to a user defined function in SQL,

CREATE FUNCTION vLastFirstNameLookUp ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT pLastName+', '+pFirstName AS Name, pMedicalRecord, pLastName, pFirstName
FROM pInfo
ORDER BY pLastName, pFirstName)

The front end is a VB app that uses an Active-X User Connection to call the function in the SQL DB. Here is the error I receive when open is called,

Run-time error '-2147217900 (80040e14)':

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '`'.

The problem is that I don't see a ` anywhere. The Data Link Properties are set for OLE DB Providers for ODBC Providers.

Thanks
 
Old August 4th, 2004, 06:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Can you post the code that calls the function from the frontend?

_________________________
- Vijay G
Strive for Perfection
 
Old August 4th, 2004, 04:34 PM
Hez Hez is offline
Registered User
 
Join Date: Aug 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dim de As deSearchItems

Set de = New deSearchItems
' The error occurs on the following line.
de.rscmdNameLookup.Open


'----------------------------------
' This is the only code in deSerchItems, there is a DEConnection
' called con in deSearchItems.

Private Sub DataEnvironment_Initialize()
    con.ConnectionString = g_ConStr
End Sub

'----------------------------------
' From main.bas, g_ConStr is global
g_ConStr = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=apicdsn;uid=;pwd=;"


I put a breakpoint on con.ConnectionString = g_ConStr. When it hit the breakpoint, I just hit run and the error does not occur.

Thanks,
Hez
 
Old August 5th, 2004, 08:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi there,

Not sure where you call the SQL function in that code?
de.rscmdNameLookup.Open

Also, can you change this part of your query to not use ' and see if that works fine. But IMO, that query is perfect. just wanted to rule that possibilty out.

SELECT TOP 100 PERCENT pFirstName AS Name, pMedicalRecord, pLastName, pFirstName FROM pInfo ORDER BY pLastName, pFirstName

And then try this, if the previous one doesn't fail.

SELECT TOP 100 PERCENT pLastName+ ' ' +pFirstName AS Name, pMedicalRecord, pLastName, pFirstName FROM pInfo
ORDER BY pLastName, pFirstName

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection




Similar Threads
Thread Thread Starter Forum Replies Last Post
Incorrect syntax near '.' meraj ADO.NET 6 February 27th, 2012 01:01 AM
Incorrect syntax near 'FULLTEXT'? Ron Howerton SQL Server 2005 5 August 30th, 2006 03:39 PM
Incorrect syntax near '!' sinapra Classic ASP Databases 8 August 25th, 2004 02:15 AM
Incorrect syntax...... Adam H-W Classic ASP Databases 8 June 21st, 2004 11:14 AM
Incorrect SQL Syntax??! ckudrna Access ASP 1 June 5th, 2003 01:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.