Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ADO Me.RecordSet invalid recordset property

Posted on 2011-10-06
6
Medium Priority
?
761 Views
Last Modified: 2012-06-27
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
Comment
Question by:Gite
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Preece
ID: 36925737
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
 
LVL 12

Expert Comment

by:Preece
ID: 36925747
Or you can use two dashes for a single line comment:

-- single line comment in sql
0
 

Author Comment

by:Gite
ID: 36925865
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:Preece
ID: 36925938
My bad.  Your example has a mix of sql and a vb function.  Still looking...

Preece
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 2000 total points
ID: 36928858
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
 

Author Closing Comment

by:Gite
ID: 36931389
Great. The problem was with the .execute!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question