VB6 - Query/StoredProc works in Access but not in VB6 - please help!

jonifen — Fri, 20 Nov 2009 00:01:07 GMT

Hi,

I've got a bit of a problem with an application I am writing at the moment. I know it has to be something small which I'm missing, but I can't figure out what it is.

Basically, I have a Query (or Stored Procedure if you prefer) in an Access2000 database called qryApptList which has one parameter (DateOfAppt). This parameter is Text (as is the Date field in the database table).

If I run the query in Access, it prompts for the Date. I enter it in and it returns my results as it should. If I then do the same thing in VB6 using ADODB, it brings back nothing.

My VB6 code...

Private m_oConnection As ADODB.Connection

Private m_oCmdGetAppointments As ADODB.Command



'Open the database connection

Public Sub OpenDatabaseConnection()

    On Error GoTo ErrorHandler

    

    If Len(m_sDatabasePath) = 0 Then

        m_sDatabasePath = m_oParentForm.FileSelection

    End If

    

    Set m_oConnection = New ADODB.Connection

    

    m_oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                       "Data Source=" & m_sDatabasePath

    

    If m_oConnection.State <> adStateOpen Then

        Set m_oConnection = Nothing

        MsgBox "Could not open connection to database", vbCritical + vbOKOnly, App.Title

        Exit Sub

    End If

    

    Exit Sub

ErrorHandler:

    HandleError "clsDatabaseHandler:OpenDatabaseConnection Sub"

End Sub





'Method to get the appointments from the database

Public Function GetAppointments(ByVal sDate As String, ByRef oRsAppts As ADODB.Recordset)

    On Error GoTo ErrorHandler

    

    If m_oCmdGetAppointments Is Nothing Then

        Set m_oCmdGetAppointments = New ADODB.Command

        

        With m_oCmdGetAppointments

            Set .ActiveConnection = m_oConnection

            

            .CommandText = "qryApptList"

            .CommandType = adCmdStoredProc

            

            .Parameters.Append .CreateParameter("DateOfAppt", adVarChar, adParamInput, 11)

        End With

    End If

    

    m_oCmdGetAppointments.Parameters("DateOfAppt") = sDate

    

    Set oRsAppts = New ADODB.Recordset

    

    oRsAppts.Open m_oCmdGetAppointments, , adOpenStatic, adLockReadOnly

    

    Exit Function

ErrorHandler:

    HandleError "clsDatabaseHandler:GetAppointments Function"

End Function

All this code runs within a separate class module which is called from a form.

And if it helps... my SQL query.

PARAMETERS DateOfAppt Text ( 255 );

SELECT tblAppointments.ApptId, tblAppointments.User, tblAppointments.ApptDate, tblAppointments.ApptTime, tblAppointments.ClientId, tblAppointments.Comments, tblClients.Title, tblClients.FirstName, tblClients.Surname, tblClients.ContactNumber1

FROM tblClients INNER JOIN tblAppointments ON tblClients.ClientId=tblAppointments.ClientId

WHERE tblAppointments.ApptDate=[DateOfAppt];

I have had it working once and I had a recordcount of 1 in the recordset (I only have one record in the table by the way). I haven't knowingly changed anything but now I get 0 records every time.

Any help would be massively appreciated as I'm tearing out what little hair I have left!

DaniWeb IT Discussion Community

VB6 - Query/StoredProc works in Access but not in VB6 - please help!