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

[jonc]

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!

[jonc]

For info, I have also tried it without the ADODB.Command object and hard-coded the SQL statement before opening the recordset against it but I still get nothing back from the database. As said before, the SQL query works perfectly in Access so I'm at a real loss here... has anyone got any ideas?

[jonc]

After much hair pulling and head scratching, I've got it working and believe it or not, it was a complete schoolboy error as I didn't clean up afterwards and destroy the command object so it was holding the original parameter value.

So I've moved the command from being module based to just method based (don't know why I had it in the module to start with!) and the amended code is as follows:

Public Function GetAppointments(ByVal sDate As String, ByRef oRsAppts As ADODB.Recordset)
    On Error GoTo ErrorHandler
    
    Dim oCommand As ADODB.Command
    
    If oCommand Is Nothing Then
        Set oCommand = New ADODB.Command
        
        With oCommand
            Set .ActiveConnection = m_oConnection
            
            .CommandText = "qryApptList"
            .CommandType = adCmdStoredProc
            
            .Parameters.Append .CreateParameter("DateOfAppt", adVarChar, adParamInput, 20)
        End With
    End If
    
    oCommand.Parameters("DateOfAppt") = sDate
    
    Set oRsAppts = New ADODB.Recordset
    
    oRsAppts.CursorLocation = adUseClient
    
    oRsAppts.Open oCommand, , adOpenStatic, adLockReadOnly
    
    Set oCommand = Nothing
    
    Exit Function
ErrorHandler:
    HandleError "clsDatabaseHandler:GetAppointments Function"
End Function

Thanks to anyone who looked in.

[mallak alrooh]

Hi :
I have this problem, and I'm trying to solve it, I need your help#####@@@

write aprogramto generate a business travel express attaachment for an income tax return. The program should request ias input the name of the organization visited, the date and location of the visit,and the expenses for meals and entertainment,airplane fare,loding,and taxi fares.(Only 50% of the expenses for meals and entertainment are deductible) . Sub procedures should be used for the input and output .

[jonc]

Hi :
I have this problem, and I'm trying to solve it, I need your help#####@@@

write aprogramto generate a business travel express attaachment for an income tax return. The program should request ias input the name of the organization visited, the date and location of the visit,and the expenses for meals and entertainment,airplane fare,loding,and taxi fares.(Only 50% of the expenses for meals and entertainment are deductible) . Sub procedures should be used for the input and output .

It's not really that similar to my original query and therefore your question would be better placed in its own thread.
However, I'd recommend doing some research and finding the points that you think you'll have problems with. If you have code that you have written already that is causing you problems then even better, you could post it up and people can then see if they can spot the error or improve it in any way.

As it stands, it appears (from your post) that you are hoping for someone to provide you with the code to work with.