Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Professional For advanced coder questions in ASP 3. 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 Professional 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 Search this Thread Display Modes
  #1 (permalink)  
Old November 10th, 2015, 08:29 AM
Authorized User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default error query sql

With this select I do various calculations. I have only used it in MYSQL works perfect.
Code:
select nome, ((select count(*) from JOGOS where timea=times.nome)+(select count(*) from JOGOS where timeb=times.nome)) as qtdejogo,(((select count(*) from JOGOS where timea=times.nome and resultadoa>resultadob)+(select count(*) from JOGOS where timeb=times.nome and resultadoa<resultadob))*3)+(((select count(*) from JOGOS where timea=times.nome and resultadoa=resultadob)+(select count(*) from JOGOS where timeb=times.nome and resultadoa=resultadob))*1) as ponto from times order by ponto desc
BD ACCESS, if I leave with the error of the ORDER BY POINT:
Code:
select nome, ((select count(*) from JOGOS where timea=times.nome)+(select count(*) from JOGOS where timeb=times.nome)) as qtdejogo,(((select count(*) from JOGOS where timea=times.nome and resultadoa>resultadob)+(select count(*) from JOGOS where timeb=times.nome and resultadoa<resultadob))*3)+(((select count(*) from JOGOS where timea=times.nome and resultadoa=resultadob)+(select count(*) from JOGOS where timeb=times.nome and resultadoa=resultadob))*1) as 'ponto' from times
What's wrong with this select?
Reply With Quote
  #2 (permalink)  
Old November 10th, 2015, 08:51 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

shouldn't:

as 'ponto'

be:

as ponto

FYI your post is a bit unclear. In future you should post the actual error for the best help. Also what does BD ACCESS mean? I assume your query is not working in Access? If so, use the analyser, break it down, run your sub selects separate. Then build it up
__________________
Wind is your friend
Matt
Reply With Quote
  #3 (permalink)  
Old November 10th, 2015, 09:37 PM
Authorized User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Matt, I'm using a database in access.

When insert this:
Code:
set rs = con.execute("select nome, ((select count(*) from JOGOS where TIMEA=times.nome)+(select count(*) from JOGOS where TIMEB=times.nome)) as qtdejogo,(((select count(*) from JOGOS where TIMEA=times.nome and resultadoa>resultadob)+(select count(*) from JOGOS where TIMEB=times.nome and resultadoa<resultadob))*3)+(((select count(*) from JOGOS where TIMEA=times.nome and resultadoa=resultadob)+(select count(*) from JOGOS where TIMEB=times.nome and resultadoa=resultadob))*1) as ponto from times order by ponto desc")
Generates error:
Code:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft] [ODBC Driver for Microsoft Access Driver] Too few parameters. They were expected 1.
/bia/tabela.asp, line 23

but when I insert in this way:
Code:
set rs = con.execute("select nome, ((select count(*) from JOGOS where TIMEA=times.nome)+(select count(*) from JOGOS where TIMEB=times.nome)) as qtdejogo,(((select count(*) from JOGOS where TIMEA=times.nome and resultadoa>resultadob)+(select count(*) from JOGOS where TIMEB=times.nome and resultadoa<resultadob))*3)+(((select count(*) from JOGOS where TIMEA=times.nome and resultadoa=resultadob)+(select count(*) from JOGOS where TIMEB=times.nome and resultadoa=resultadob))*1) as ponto from times order by 'ponto' desc")
Works but does not sort in decreasing order the column. Gets all scrambled.
Reply With Quote
  #4 (permalink)  
Old November 10th, 2015, 09:59 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

I dont use Access and have not for many years. I do remember this:

Too few parameters. They were expected 1.

was thrown as a result of a field name issue. field doesn't exist from memory.

The best way to resolve this type of issue is to do what I told you to in my last post. Strip your query back, run it, add another piece, run it, etc..... then you will find the bit that is problematic.

Another question I asked in my first post , shouldnt:

order by 'ponto' desc

be

order by ponto desc
__________________
Wind is your friend
Matt
Reply With Quote
  #5 (permalink)  
Old November 12th, 2015, 06:56 AM
Authorized User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Matt, how are you?

I tested the select the MS Access as follows:

http://www.hudsonalves.com/mercadolivreimg/print1.png


When running SQL him behind the table but does not order the column "ponto":

http://www.hudsonalves.com/mercadolivreimg/print2.png
Reply With Quote
  #6 (permalink)  
Old November 12th, 2015, 07:24 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

HI there. I am well thank you.

I keep asking why do you surround ponto in single quotes, IMO this is not necessary and not recommended. This said its not your solution. It must be your datatype, what is the data type. Based on your output surely it cant be a number?
__________________
Wind is your friend
Matt
Reply With Quote
  #7 (permalink)  
Old November 12th, 2015, 07:52 PM
Authorized User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Matt,

I noticed the following in MYSQL I can turn the end of the passage this way:

Code:
as ponto from times order by ponto desc"
The system not of error and can arrange in descending order.


In MS Access it does not work well, so no date error if I use this:
Code:
as ponto from times order by 'ponto' desc"

In this system I have to use MS Access and I'm not really finding solution to this problem.


You mentioned the output be applied with number, no problem since the organization in descending order not to work. The code I posted you think of to improve something?
Reply With Quote
  #8 (permalink)  
Old November 12th, 2015, 08:07 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

regarding your ordering issue, my question was what is the SQLServer column data type for the ponto field?
__________________
Wind is your friend
Matt
Reply With Quote
  #9 (permalink)  
Old November 12th, 2015, 09:04 PM
Authorized User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The column point collects numbers. really add the result of games. is it possible for me to include the access base in here to see you?
Reply With Quote
  #10 (permalink)  
Old November 13th, 2015, 07:30 AM
Authorized User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Matt,

If it is not asking too much, you could take a look at MS Access? Follow the link to download.

I tested it in the SQL editor

http://www.hudsonalves.com/mercadolivreimg/jogos.zip
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
SQL Update query (error message) Neal SQL Server 2000 1 August 3rd, 2007 08:57 PM
SQL query on Access db giving error DesignsOnline.co.uk Access 2 November 17th, 2006 05:06 PM
Error in SQL INSERT query lawsoncobol Access VBA 1 August 4th, 2006 04:25 AM
SQL Query returns error Raphasevilla Access VBA 2 February 22nd, 2006 08:30 AM



All times are GMT -4. The time now is 09:27 AM.


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