Problem with SUM clause via ODBC

------=_NextPart_000_0005_01C5A57D.CE71B2A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

My configuration is:
MySQL Query Browser 1.1.13
MySQL 5.0.10a-beta-nt
MySQL client 5.0.4
MySQL ODBC 5.51 Driver 3.51.11.00
Windows 2000 Pro
MDAC_Type 2.81


This simple query
"SELECT SUM(sub_tot) as totale FROM rg"
work fine on MySQL Query Browser 1.1.13
result 3456.87

when I execute the same query in VB 6.0

Dim MySQLConn As ADODB.Connection
Set MySQLConn =3D New ADODB.Connection
MySQLConn.Open "MySql_collettore"
=20
Dim rst As New ADODB.Recordset
=20
rst.Open "SELECT SUM(sub_tot) as totale FROM rg", MySQLConn
Do While Not rst.EOF
MsgBox rst("totale")
rst.MoveNext
Loop

it return an empty recordset rst.

Why ???
------=_NextPart_000_0005_01C5A57D.CE71B2A0--

Re: Problem with SUM clause via ODBC

I don't know if there's anything wrong with your ODBC driver, but for
sure in your VB piece of code you should replace your do while ... loop
statement with a if then ... end if, since you only get one row from
your sql query.

e.didomenico@sinelogo.it ha scritto:

>My configuration is:
>MySQL Query Browser 1.1.13
>MySQL 5.0.10a-beta-nt
>MySQL client 5.0.4
>MySQL ODBC 5.51 Driver 3.51.11.00
>Windows 2000 Pro
>MDAC_Type 2.81
>
>
>This simple query
>"SELECT SUM(sub_tot) as totale FROM rg"
>work fine on MySQL Query Browser 1.1.13
>result 3456.87
>
>when I execute the same query in VB 6.0
>
>Dim MySQLConn As ADODB.Connection
>Set MySQLConn = New ADODB.Connection
>MySQLConn.Open "MySql_collettore"
>
>Dim rst As New ADODB.Recordset
>
>rst.Open "SELECT SUM(sub_tot) as totale FROM rg", MySQLConn
>Do While Not rst.EOF
> MsgBox rst("totale")
>rst.MoveNext
>Loop
>
>it return an empty recordset rst.
>
>Why ???
>
>
>----------------------------------------------------------- -------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.338 / Virus Database: 267.10.13/78 - Release Date: 19/08/2005
>
>



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.13/78 - Release Date: 19/08/2005


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: Problem with SUM clause via ODBC

--------------090804020408040308040301
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

1.) remove the while wend
2.) change the sql com (not sure if this maybe the reason, but "totale"
maybe a reserved keyword - I dont know - so remove it to be sure it is
not the prob)
3.) try the following piece of code

Dim MySQLConn As ADODB.Connection
Set MySQLConn = New ADODB.Connection
MySQLConn.Open "MySql_collettore"

dim rst as new adodb.recordset
Set rst = New ADODB.Recordset

rst.Open "SELECT SUM(sub_tot) FROM rg", MySQLConn

If rst.RecordCount > 0 Then
rst.movefirst
if isnull(rst.fields(0).value) = false then
msgbox rst.fields(0).value
else
msgbox "the field value is null - somethings really wrong about it"
endif
endif


Maybe this helps
Michael




e.didomenico@sinelogo.it schrieb:

>My configuration is:
>MySQL Query Browser 1.1.13
>MySQL 5.0.10a-beta-nt
>MySQL client 5.0.4
>MySQL ODBC 5.51 Driver 3.51.11.00
>Windows 2000 Pro
>MDAC_Type 2.81
>
>
>This simple query
>"SELECT SUM(sub_tot) as totale FROM rg"
>work fine on MySQL Query Browser 1.1.13
>result 3456.87
>
>when I execute the same query in VB 6.0
>
>Dim MySQLConn As ADODB.Connection
>Set MySQLConn = New ADODB.Connection
>MySQLConn.Open "MySql_collettore"
>
>Dim rst As New ADODB.Recordset
>
>rst.Open "SELECT SUM(sub_tot) as totale FROM rg", MySQLConn
>Do While Not rst.EOF
> MsgBox rst("totale")
>rst.MoveNext
>Loop
>
>it return an empty recordset rst.
>
>Why ???
>
>

--
Michael Haunzwickl, Geschäftsleitung
SecureYourNetworks
Langobardenstraße 191/14
AT - 1220 Wien

Tel.: +43/699/11686344



--------------090804020408040308040301--

Re: Problem with SUM clause via ODBC

------=_NextPart_000_002E_01C5A590.A2A5B690
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Sorry but don't work.

It return rst.recordcount=3D-1 so the recordset is empty.
The worderful think is that if I execute the same query via
MySql Query Browser it return one record.
So if I link the table in MSAccess the query passthru return one record.

The same problem is with AVG clause, but work with MIN, MAX clause.

Tank
----- Original Message -----=20
From: Michael Haunzwickl / SYN=20
To: e.didomenico@sinelogo.it=20
Cc: myodbc@lists.mysql.com=20
Sent: Saturday, August 20, 2005 1:57 PM
Subject: Re: Problem with SUM clause via ODBC


1.) remove the while wend
2.) change the sql com (not sure if this maybe the reason, but =
"totale" maybe a reserved keyword - I dont know - so remove it to be =
sure it is not the prob)
3.) try the following piece of code


Dim MySQLConn As ADODB.Connection
Set MySQLConn =3D New ADODB.Connection
MySQLConn.Open "MySql_collettore"
dim rst as new adodb.recordset
Set rst =3D New ADODB.Recordset


rst.Open "SELECT SUM(sub_tot) FROM rg", MySQLConnIf rst.RecordCount > 0 =
Then
rst.movefirst
if isnull(rst.fields(0).value) =3D false then=20
msgbox rst.fields(0).value=20
else
msgbox "the field value is null - somethings really wrong about =
it"
endif
endif


Maybe this helps
Michael




e.didomenico@sinelogo.it schrieb:=20
My configuration is:
MySQL Query Browser 1.1.13
MySQL 5.0.10a-beta-nt
MySQL client 5.0.4
MySQL ODBC 5.51 Driver 3.51.11.00
Windows 2000 Pro
MDAC_Type 2.81


This simple query
"SELECT SUM(sub_tot) as totale FROM rg"
work fine on MySQL Query Browser 1.1.13
result 3456.87

when I execute the same query in VB 6.0

Dim MySQLConn As ADODB.Connection
Set MySQLConn =3D New ADODB.Connection
MySQLConn.Open "MySql_collettore"
=20
Dim rst As New ADODB.Recordset
=20
rst.Open "SELECT SUM(sub_tot) as totale FROM rg", MySQLConn
Do While Not rst.EOF
MsgBox rst("totale")
rst.MoveNext
Loop

it return an empty recordset rst.

Why ???
=20

--=20
Michael Haunzwickl, Geschäftsleitung
SecureYourNetworks
Langobardenstraße 191/14
AT - 1220 Wien

Tel.: +43/699/11686344


------=_NextPart_000_002E_01C5A590.A2A5B690--

Re: Problem with SUM clause via ODBC

e.didomenico@sinelogo.it wrote:

>when I execute the same query in VB 6.0
>
>Dim MySQLConn As ADODB.Connection
>Set MySQLConn = New ADODB.Connection
>MySQLConn.Open "MySql_collettore"
>
>Dim rst As New ADODB.Recordset
>
>rst.Open "SELECT SUM(sub_tot) as totale FROM rg", MySQLConn
>
>
You haven't set up your objects. You can't just rely on the default
values for the connection and recordset objects. You should do at *least*:

Set MySQLConn = New ADODB.Connection

With MySQLConn
.ConnectionString = "xxxxxxxxxxx" ' Put your connection string here
.CursorLocation = adUseClient
.Open
End With

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = MySQLConn
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open "SELECT SUM(sub_tot) as totale FROM rg"
End With

The most important is the CursorLocation. In some ( most ) cases, you
must use adUseClient, whereas in others, you must use adUseServer (
uncommon, but use in cases where adUseClient fails ). If you don't set
this properly, you won't get the number or records back into VB
properly, and things like rst.RecordCount, rst.EOF etc won't work properly.

Read up on cursor and lock types. The above example will give you a
fast, non-blocking query ( read-only ). I strongly recommend that you
perform ALL queries in this mode, or if you're only going to walk
through a recordset from start to end, you can use .CursorType =
adOpenForwardOnly. If you have to make changes to a recordset, don't
open it and edit the recordset directly - in this case, create a command
object and send all your updates to MySQL as 'update table' commands.

Dan

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: Problem with SUM clause via ODBC

e,

> It return rst.recordcount=-1 so the recordset is empty.
I'm not sure - I think "recordcount=-1" means NOT EMPTY (as opposed to
0=empty).
Could you try the following:

rst.MoveLast
msgbox "Records " & rst.Recordcount
rst.MoveFirst

to be sure you get an empty recordset ?

Markus

----- Original Message -----
From:
To: "Michael Haunzwickl / SYN"
Cc:
Sent: Saturday, August 20, 2005 2:08 PM
Subject: Re: Problem with SUM clause via ODBC


Sorry but don't work.

It return rst.recordcount=-1 so the recordset is empty.
The worderful think is that if I execute the same query via
MySql Query Browser it return one record.
So if I link the table in MSAccess the query passthru return one record.

The same problem is with AVG clause, but work with MIN, MAX clause.

Tank
----- Original Message -----
From: Michael Haunzwickl / SYN
To: e.didomenico@sinelogo.it
Cc: myodbc@lists.mysql.com
Sent: Saturday, August 20, 2005 1:57 PM
Subject: Re: Problem with SUM clause via ODBC


1.) remove the while wend
2.) change the sql com (not sure if this maybe the reason, but "totale"
maybe a reserved keyword - I dont know - so remove it to be sure it is not
the prob)
3.) try the following piece of code


Dim MySQLConn As ADODB.Connection
Set MySQLConn = New ADODB.Connection
MySQLConn.Open "MySql_collettore"
dim rst as new adodb.recordset
Set rst = New ADODB.Recordset


rst.Open "SELECT SUM(sub_tot) FROM rg", MySQLConnIf rst.RecordCount > 0 Then
rst.movefirst
if isnull(rst.fields(0).value) = false then
msgbox rst.fields(0).value
else
msgbox "the field value is null - somethings really wrong about it"
endif
endif


Maybe this helps
Michael




e.didomenico@sinelogo.it schrieb:
My configuration is:
MySQL Query Browser 1.1.13
MySQL 5.0.10a-beta-nt
MySQL client 5.0.4
MySQL ODBC 5.51 Driver 3.51.11.00
Windows 2000 Pro
MDAC_Type 2.81


This simple query
"SELECT SUM(sub_tot) as totale FROM rg"
work fine on MySQL Query Browser 1.1.13
result 3456.87

when I execute the same query in VB 6.0

Dim MySQLConn As ADODB.Connection
Set MySQLConn = New ADODB.Connection
MySQLConn.Open "MySql_collettore"

Dim rst As New ADODB.Recordset

rst.Open "SELECT SUM(sub_tot) as totale FROM rg", MySQLConn
Do While Not rst.EOF
MsgBox rst("totale")
rst.MoveNext
Loop

it return an empty recordset rst.

Why ???


--
Michael Haunzwickl, Geschäftsleitung
SecureYourNetworks
Langobardenstraße 191/14
AT - 1220 Wien

Tel.: +43/699/11686344





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org