Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 24th, 2004, 04:05 AM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default Incorrect syntax near '!'

I am getting the error mentioned on the subject line when running this query on the front end. I am providing the query to make it easy. Can anyone help me on this issue!!
Regards

sinapra
__________________
sinapra
Reply With Quote
  #2 (permalink)  
Old August 24th, 2004, 04:09 AM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default

sorry guys forgot to post the query : here it is

'SELECT People.People, People.Name,
Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#8/20/2004#,1,0))) AS Created,
Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#8/20/2004#,IIf(IsNull([ProjectStatusReport]![Accomplishments])
Or IsNull([ProjectStatusReport]![Plans]),0,1),0))) AS Complete, People.email FROM (((Register INNER JOIN SR_Status
ON Register.SR_Status = SR_Status.SR_Status) LEFT JOIN ProjectStatusReport
ON Register.Register = ProjectStatusReport.Project) INNER JOIN StakeHolders
ON Register.Register = StakeHolders.Register) INNER JOIN People
ON StakeHolders.People = People.People WHERE (((SR_Status.Status)='Active') AND ((StakeHolders.Status)=Yes))
GROUP BY People.People, People.Name, People.email
HAVING (((Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#8/20/2004#,1,0))))>0)
AND ((Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#8/20/2004#,IIf(IsNull([ProjectStatusReport]![Accomplishments])
Or IsNull([ProjectStatusReport]![Plans]),0,1),0))))>0)) ORDER BY People.People'

thank u

sinapra
Reply With Quote
  #3 (permalink)  
Old August 24th, 2004, 06:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Not sure what database you use.(access/sql server)

Replace all ! with .

Using DOT here - People.People, but wondering why you are using ! in the next line - [ProjectStatusReport]![Week];)

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #4 (permalink)  
Old August 24th, 2004, 06:31 AM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default

Hi Vijay

I had the same thing in my mind, but the script was written by the support guys in my team long back. I may not be able to change this but is there anything that could explain me the significance of '!'.

Thank you and Regards

sinapra
Reply With Quote
  #5 (permalink)  
Old August 24th, 2004, 06:33 AM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default

forgot to mention, we are using SQL server

thanks again and regards

sinapra
Reply With Quote
  #6 (permalink)  
Old August 24th, 2004, 10:12 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

hmmm, as it says "INCORRECT SYNTAX NEAR !", that is more than enough to explain. I really wonder, why part of it was used with DOT and remaining with !.

In SQL server you cannot use ! as for referencing objects.

. is used for object referencing

Eg: TableName.ColumnName is how you can reference in sql.
If I am right, ! is VB code that is used to do the same as DOT does.

So you got to change all !s to DOTs

Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #7 (permalink)  
Old August 24th, 2004, 11:21 PM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default

Thanks Vijay,
it helps
cheers

sinapra
Reply With Quote
  #8 (permalink)  
Old August 25th, 2004, 12:39 AM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Looks like you are using an Access Project file??? If that is the case, [ProjectStatusReport]![Week] most likely references an open report called ProjectStatusReport and a control on that report called Week.

Do you have a report named ProjectStatusReport?
Is that report open when the query is run?

Reply With Quote
  #9 (permalink)  
Old August 25th, 2004, 02:15 AM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default

hi U.N.C.L.E.
ProjectStatusReport is not a report, its a table name and week is the field name of date type. I would need to clarify this from my support and get back to you so that I can explain the usage of '!' in the query.
Regards

sinapra
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Incorrect syntax near '.' meraj ADO.NET 6 February 27th, 2012 12:01 AM
Incorrect syntax near 'FULLTEXT'? Ron Howerton SQL Server 2005 5 August 30th, 2006 03:39 PM
Incorrect syntax near '`' Hez Classic ASP Databases 3 August 5th, 2004 08:13 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



All times are GMT -4. The time now is 04:26 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.