PDA

View Full Version : Problem with VBA script reading from MySql database



kryten
07-21-2011, 07:53 AM
I am having some trouble with a vba script in Excel which should be reading from a MySql database. The SQL query should only return one record but actually returns an empty resultset. The generated statement works fine when run through phpMyAdmin.

Here is my code:

Function getClientId(emailAddress As String)
Dim rs As ADODB.Recordset
Dim sql As String

ConnectDB

Set rs = New ADODB.Recordset

sql = "SELECT client_id FROM clients WHERE email_address = '" & emailAddress & "' LIMIT 1"
Debug.Print sql
rs.Open sql, oConn

Debug.Print rs.RecordCount

If (rs.RecordCount = -1) Then
getClientId = Null
Else
getClientId = rs(0)
End If
rs.Close
End Function
My database connect function.

Function ConnectDB()
On Error GoTo ErrHandler

Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=mydb;" & _
"USER=user;" & _
"PASSWORD=password;" & _
"Option=3"

'Debug.Print oConn

Exit Function
ErrHandler:
MsgBox Err.Description, vbCritical, Err.Source
End Function
The ConnectDB function is connecting ok as I am running other scripts with it. If anyone can see what I am doing wrong then any help would be appreciated.

Many thanks in advance.

Garry

Kenneth Hobs
07-21-2011, 11:03 AM
Welcome to the forum!

Maybe LCase() or UCase() is needed in the sql string? Try leaving the LIMIT off for testing.

mohanvijay
07-22-2011, 01:17 AM
change rs.open method like below



rs.Open Sql, oConn, adOpenStatic, adLockReadOnly

kryten
07-22-2011, 03:25 AM
Thanks for the answers guys.

It turns out the answer was that rs.RecordCount was always returning -1 rather than the record count. By changing
If (rs.RecordCount = -1) Then
getClientId = Null
Else
getClientId = rs(0)
End If
to
If (rs(0) < 1) Then
getClientId = Null
Else
getClientId = rs(0)
End If
everything worked fine.