• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

ADO Me.RecordSet invalid recordset property

I have a form which is not updatable and based on an ado recordset (see code)
Previously, the commented section was not commented, and the with cmd section wasn't there. It worked fine.
Recently, Il replaced it with the cmd section and it doesn't work any more.
On the insruction Set Me.Recordset = rst, I get  an error message "7965 The object you entered is not a valid recordset property"
What is wrong?

In the code, I have also listed the SQL Server (2008) stored procedure being used in the with cmd section. The field is NORecherche is unique.
Private Sub Form_Open(Cancel As Integer)
    Dim SQL As String
    Dim cmd As New ADODB.Command
    Dim rst As New ADODB.Recordset
    If cnUtil.State <> adStateOpen Then
        cnUtil.Open sDemelerChaine(sConnUtil)
    End If
On Error GoTo Terminer
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenKeyset
'    rst.Open SQL, cnUtil, adOpenKeyset
'    SQL = "SELECT     dbo.tbRechercheExperts.ID, dbo.tbRechercheExperts.NoRecherche, dbo.tbRechercheExperts.Initiales,dbo.tbRechercheExperts.NbBons, dbo.tbRechercheExperts.DateReponse,dbo.tbRechercheExperts.Statut, " & _
'                      " dbo.tbRechercheExperts.DateDemande, CASE WHEN FC.NoFiche IS NULL THEN H.Prénom + ' ' + H.Nom ELSE FC.PrénHomme + ' ' + FC.NomHomme END AS Homme," & _
'                      " CASE WHEN FC.NoFiche IS NULL THEN F.Prénom + ' ' + F.Nom ELSE FC.PrénFemme + ' ' + FC.NomFemme END AS Femme, CASE WHEN FC.NoFiche IS NULL" & _
'                      " THEN TypeRecherche ELSE 3 END AS Type" & _
'        " FROM         dbo.tbRechercheExperts  LEFT OUTER JOIN" & _
'                      " [ma-arbres" & sSourceBD & "].dbo.tbPersonnes AS H ON dbo.tbRechercheExperts.NoHomme = H.NoPersonne LEFT OUTER JOIN" & _
'                      " [ma-arbres" & sSourceBD & "].dbo.tbPersonnes AS F ON dbo.tbRechercheExperts.NoFemme = F.NoPersonne LEFT OUTER JOIN" & _
'                      " [mesaieux" & sSourceBD & "].dbo.tbSiteFiches AS FC ON dbo.tbRechercheExperts.NoFiche = FC.NoFiche" & _
'        " WHERE     (dbo.tbRechercheExperts.ID = '" & Me.OpenArgs & "')"
    With cmd
        .ActiveConnection = cnUtil
        .CommandType = adCmdStoredProc
        .CommandText = "spRecherchesServClientele"
        .Parameters.Append cmd.CreateParameter("@ID", adVarChar, adParamInput, 20, Me.OpenArgs)
        Set rst = .Execute
    End With
    
    
    
    Set Me.Recordset = rst
    Exit Sub
Terminer:
    Cancel = True

End Sub




USE [ma-admin-01]
GO
/****** Object:  StoredProcedure [dbo].[spRecherchesServClientele]    Script Date: 10/06/2011 12:15:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spRecherchesServClientele] 
	-- Add the parameters for the stored procedure here
	@ID varchar(20) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT     dbo.tbRechercheExperts.ID, dbo.tbRechercheExperts.NoRecherche, dbo.tbRechercheExperts.Initiales,dbo.tbRechercheExperts.NbBons, 
	dbo.tbRechercheExperts.DateReponse,dbo.tbRechercheExperts.Statut,
	dbo.tbRechercheExperts.DateDemande, 
	CASE WHEN tbRechercheExperts.NoFiche IS NULL THEN 
	 CASE WHEN TypeRecherche IN (0, 1) THEN H.Prénom + ' ' + H.Nom 
	 ELSE CASE E.Sexe WHEN 1 THEN E.Prénom + ' ' + E.Nom ELSE '' END
	 END
	ELSE 
	 CASE tbRechercheExperts.TypeRecherche WHEN 1 THEN FC.PrénHomme + ' ' + FC.NomHomme 
	 WHEN 2 THEN CASE B.Sexe WHEN 1 THEN B.PrénEnfant + ' ' + B.NomEnfant ELSE '' END
	 WHEN 3 THEN CASE S.Sexe WHEN 1 THEN S.PrénDéfunt + ' ' + S.NomDéfunt ELSE '' END
	 END
	END AS Homme,
	CASE WHEN tbRechercheExperts.NoFiche IS NULL THEN 
	 CASE WHEN TypeRecherche IN (0, 1) THEN F.Prénom + ' ' + F.Nom 
	 ELSE CASE E.Sexe WHEN 0 THEN E.Prénom + ' ' + E.Nom ELSE '' END
	 END
	ELSE 
	 CASE tbRechercheExperts.TypeRecherche WHEN 1 THEN FC.PrénFemme + ' ' + FC.NomFemme  
	 WHEN 2 THEN CASE B.Sexe WHEN 0 THEN B.PrénEnfant + ' ' + B.NomEnfant ELSE '' END
	 WHEN 3 THEN CASE S.Sexe WHEN 0 THEN S.PrénDéfunt + ' ' + S.NomDéfunt ELSE '' END
	 END 
	END AS Femme, 
	CASE WHEN tbRechercheExperts.NoFiche IS NULL THEN CASE WHEN TypeRecherche BETWEEN 1 AND 3 THEN  1 ELSE 0 END ELSE 2 END AS TypeRecherche,
	CASE WHEN TypeRecherche IN (0, 1) THEN  'M' WHEN TypeRecherche IN (2, 4) THEN  'B' ELSE 'S' END AS TypeActe
	FROM dbo.tbRechercheExperts  
	LEFT OUTER JOIN [ma-arbres-01].dbo.tbPersonnes AS H ON dbo.tbRechercheExperts.NoHomme = H.NoPersonne 
	LEFT OUTER JOIN [ma-arbres-01].dbo.tbPersonnes AS F ON dbo.tbRechercheExperts.NoFemme = F.NoPersonne 
	LEFT OUTER JOIN [ma-arbres-01].dbo.tbPersonnes AS E ON dbo.tbRechercheExperts.NoEnfant = E.NoPersonne 
	LEFT OUTER JOIN [mesaieux-01].dbo.tbSiteFiches AS FC ON dbo.tbRechercheExperts.NoFiche = FC.NoFiche
	LEFT OUTER JOIN [ma-bs-01].dbo.tbSiteBaptemes AS B ON dbo.tbRechercheExperts.NoFiche = B.NoActe
	LEFT OUTER JOIN [ma-bs-01].dbo.tbSiteSepultures AS S ON dbo.tbRechercheExperts.NoFiche = S.NoActe
	WHERE     (dbo.tbRechercheExperts.ID = @ID)END

Open in new window

0
Gite
Asked:
Gite
  • 3
  • 2
1 Solution
 
PreeceCommented:
I think the problem might be that you are using a single quote as a comment marker.  This works in VB and VB.NET, but not sql.  Use this instead:

/*
commented code
goes here
*/


Preece
0
 
PreeceCommented:
Or you can use two dashes for a single line comment:

-- single line comment in sql
0
 
GiteAuthor Commented:
I executed the stocked procedure as is in SQL Server and it worked fine. The execute instruction works fine.
The problem is with the set Me.recordset statement
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
PreeceCommented:
My bad.  Your example has a mix of sql and a vb function.  Still looking...

Preece
0
 
Leigh PurvisDatabase DeveloperCommented:
Have you tested the recordset after opening it?  Have you examined the recordset before attempting to assign it to make sure it was successfully opened and can be accessed and navigated?
Since the only change is the recordset you've opened, you can safely look there as you start to debugging. ;-)
There is an initial concept here.
rst.CursorType = adOpenKeyset

Open in new window

You might as well actually request the more accurate
rst.CursorType = adOpenStatic

Open in new window

As this is what you'd get with a client side cursor anyway.  However, then consider how you're opening the recordset.
Set rst = .Execute

Open in new window

The Execute method normally returns a forward only recordset, although the client side cursor forces this Static again.  But always a read only one.
If you open your recordset more like:
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    rst.LockType = adLockReadOnly
    With cmd
        Set .ActiveConnection = cnUtil
        .CommandType = adCmdStoredProc
        .CommandText = "spRecherchesServClientele"
        .Parameters.Append cmd.CreateParameter("@ID", adVarChar, adParamInput, 20, Me.OpenArgs)
    End With
    rst.Open cmd
    Set Me.Recordset = rst

Open in new window

Do you get the same results?
0
 
GiteAuthor Commented:
Great. The problem was with the .execute!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now