I normally use MySQL with PHP, but I'm delving into connecting to Access
with ODBC (for a database that I already have set up at work), and I'm
running into a couple of errors. I'm just trying to do a simple query
with two columns - one a name (VARCHAR), and one an integer column.
First, if I add an ORDER BY clause, so the query looks like this:
$sql = "select tblScores.FullName, sum(tblScores.Points) as TotalPoints
from tblScores WHERE Division='MO'";
$sql = $sql." GROUP BY tblScores.FullName";
$sql= $sql." ORDER BY TotalPoints desc";
I get an ODBC error that says:
"odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Too
few parameters. Expected 1., SQL state 07001 in SQLExecDirect in
c:\program files\apache group\apache\htdocs\odbc\file.php on line 15"
However, if I remove the ORDER BY clause, the query runs (although the
data isn't sorted the way I would like). I have a feeling it might be
something in my SQL, but I can't see it.
Later, I did discover that if I use
ORDER BY sum(tblScores.Points)
instead of the alias (TotalPoints) the query works fine. That seems odd
to me, because everything I've read is that you should be able to use an
alias in an ORDER BY clause. Why would it behave that way when
connecting via ODBC and not for MySQL?
The second thing I'm noticing is that even though I'm using an integer
field, there is a decimal on the end of each number (i.e. 25.0 instead
of 25). When I run the same query using MySQL, there is no decimal
point, which is as it should be.
Can anyone explain why I am getting these errors?
Thanks.
Steve 2 3256
Steve <ra***********@hotmail.com> wrote: I normally use MySQL with PHP, but I'm delving into connecting to Access with ODBC (for a database that I already have set up at work), and I'm running into a couple of errors. I'm just trying to do a simple query with two columns - one a name (VARCHAR), and one an integer column.
First, if I add an ORDER BY clause, so the query looks like this:
$sql = "select tblScores.FullName, sum(tblScores.Points) as TotalPoints from tblScores WHERE Division='MO'"; $sql = $sql." GROUP BY tblScores.FullName"; $sql
= $sql." ORDER BY TotalPoints desc"; I get an ODBC error that says:
"odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in c:\program files\apache group\apache\htdocs\odbc\file.php on line 15"
However, if I remove the ORDER BY clause, the query runs (although the data isn't sorted the way I would like). I have a feeling it might be something in my SQL, but I can't see it.
Later, I did discover that if I use
ORDER BY sum(tblScores.Points)
instead of the alias (TotalPoints) the query works fine. That seems odd to me, because everything I've read is that you should be able to use an alias in an ORDER BY clause. Why would it behave that way when connecting via ODBC and not for MySQL?
If the alias works in MySQL, it is an extension. Standard SQL requires
that you use the full text of the field.
The second thing I'm noticing is that even though I'm using an integer field, there is a decimal on the end of each number (i.e. 25.0 instead of 25). When I run the same query using MySQL, there is no decimal point, which is as it should be.
What is the type of the Access field, exactly? Is it Currency?
Can anyone explain why I am getting these errors?
In short, because Access is a piece of crap. You would do better with
SQLite.
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Tim Roberts wrote: The second thing I'm noticing is that even though I'm using an integer field, there is a decimal on the end of each number (i.e. 25.0 instead of 25). When I run the same query using MySQL, there is no decimal point, which is as it should be.
What is the type of the Access field, exactly? Is it Currency?
No it is Integer. That's why I'm confused as to why there would be a
decimal place.
Can anyone explain why I am getting these errors?
In short, because Access is a piece of crap. You would do better with SQLite.
This was set up a few years ago, and changing it isn't an option right now.
Thanks.
Steve This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Wolfgang Kaml |
last post by:
Hello All,
I have been working on this for almost a week now and I haven't anything up
my sleeves anymore that I could test in addition or change....
Since I am not sure, if this is a Windows...
|
by: Andreas Lauffer |
last post by:
I changed from Access97 to AccessXP and I have immense performance
problems.
Details:
- Access XP MDB with Jet 4.0 ( no ADP-Project )
- Linked Tables to SQL-Server 2000 over ODBC
I used...
|
by: tina |
last post by:
Hello,
Can you hale me to define my mistake please?
I am trying to run a SQL Pass – Through Query from Access 2000 and
inside the record set I am trying to loop for a LoginID. I think
that this...
|
by: Abhi |
last post by:
FYI:
This message is for the benefit of MS Access Community. I found that
this prblem has been encounterd by many but there is hardly any place
where a complete solution is posted. So I thought...
|
by: VBSponge |
last post by:
Hi all. Need help here as I cant see whats wrong...
I have a querydef in Access 2k which execs a stored proc in a SQL
Server 2k db.
I keep getting the following error, and cant stop it for the...
|
by: Dave |
last post by:
Hey guys, I have an ODBC problem that has me stumped. I wrote a VBA
script to run in Microsoft Excel that pulls data out of an application
using that application's ODBC driver and puts it into...
|
by: Greg Strong |
last post by:
Hello All,
What is the maximum length of an ODBC pass through query?
Things work fine with the code except when I try to create a view which
is pretty complex in Oracle. I'm using a DSN...
|
by: bcbrock |
last post by:
I am trying to figure out how to query some data out of a dbase IV
(.dbf) file sitting on a remote server from my MySQL/PHP/Web Server.
Both servers are Windows Server 2003 SP2.
My...
|
by: Mudcat |
last post by:
In short what I'm trying to do is read a document using an xml parser
and then upload that data back into a database. I've got the code more
or less completed using xml.etree.ElementTree for the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |