1 {{ upvoteCount | shortNum }}
1 {{ upvoteCount | shortNum }}

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

by desertofwater Banned
0 replies
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...
PHP Code:
Code:
Private m_oConnection As ADODB.Connection 
Private m_oCmdGetAppointments As ADODB.Command 

Public Sub OpenDatabaseConnection() 
    On Error GoTo ErrorHandler 
     
    If Len(m_sDatabasePath) = 0 Then 
        m_sDatabasePath = m_oParentForm.FileSelection 
    End If 
     
    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 

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, 20) 
        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.
PHP Code:
Code:
PARAMETERS DateOfAppt Text ( 20 ); 
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!
#access #query or storedproc #solved #vb6 #works

Trending Topics