¤ Stored procedure does not allow addition of a record with rs.addrecord.
¤ Database is on SQL SERVER EXPRESS.
¤ An .adp ACCESS 2007 application is connected to it (easier to create/modify objects).
¤ Stored procedure was created with the .adp application.
¤ Any one can help? Thanks JL.
¤ Here is a piece of code that shows the same problem:
' Type 'Paramètre' pour procédures paramétrisées (stored procedure).
'
Public Type spParams
prmNom As String
prmType As DataTypeEnum
prmValeur As Variant
End Type
Public Sub Test()
Dim prm(0) As spParams
Dim rsGroupes As ADODB.Recordset
With prm(0)
.prmNom = "@LaDateD"
.prmType = ADODB.adInteger
.prmValeur = 20080626
End With ' prm(0)
Set rsGroupes = rsOpen("spGroupesLaDateD", prm)
With rsGroupes
If .RecordCount < 1 Then
.AddNew
!IDEmploye = 1
!ClientID = 8
!DateEntrée = Date
!Description = "Arseneau & amies 2"
!Nbre = 4
!DateD = 20080625
!DateDébut = #6/25/2008#
!DateFin = !DateDébut
.Update
End If
End With
End Sub
Public Function rsOpen( _
txtSPName As String, _
prm() As spParams _
) As ADODB.Recordset
On Error GoTo Erreur
Dim I As Long
Dim strErr As String
Dim cmdObj As New ADODB.Command
Dim prmObj As ADODB.Parameter
' Définir l'objet 'ADODB.Command'.
Set cmdObj = New ADODB.Command
With cmdObj
.ActiveConnection = "Provider=SQLOLEDB.1;Password=MyPassword ;Persist Security Info=True;User ID=sa ;Initial Catalog=MyDB;Data Source= SUPPORT\SQLExpress"
' Caractériser la commande.
.CommandText = txtSPName
.CommandType = adCmdStoredProc
.CommandTimeout = 15
' Définir le(s) paramètre(s) d'input.
Set prmObj = New ADODB.Parameter
For I = LBound(prm, 1) To UBound(prm, 1)
With prmObj
.Name = prm(I).prmNom
.Type = prm(I).prmType
Select Case prm(I).prmType
Case ADODB.adDate, ADODB.adInteger
.Size = 4
Case ADODB.adVarChar
.Size = Len(prm(I).prmValeur)
Case Else
MsgBox "Type de parametre inconnu: " & prm(I).prmValeur
End Select
.Direction = adParamInput
.Value = prm(I).prmValeur
End With ' prmObj
Next I
.Parameters.Append prmObj
Set rsOpen = New ADODB.Recordset
With rsOpen
Set .Source = cmdObj
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Open
End With
End With ' cmdObj
Done:
Exit Function
Erreur:
MsgBox Err.Description, vbCritical
Resume Done
End Function
¤ Stored procedure (as displayed by <modify>:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGroupesLaDateD](@LaDateD int)
AS SELECT dbo.Groupes.*
FROM dbo.Groupes
WHERE (DateD = @LaDateD)
