By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

ODBC Errors In Query

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Jul 17 '05 #2

P: n/a
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
Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.