Information générale
Catégorie:
Bases de données DAO/RDO/ODBC/ADO
>No, I do a .Parameters.Refresh -->
>With adoCommand
> adoCommand.CommandText = "sp_InsertAirMaster"
> adoCommand.Parameters.Refresh
> adoCommand.Parameters(1) = mskMAWBAir.Text
> adoCommand.Parameters(2) = 2
> (...)
> adoCommand.Parameters(16) = txtComments.Text
>
> Set adoRecord = adoCommand.Execute
>
> MsgBox adoRecord!PK_MasterID --> ERROR
>End With
>
>Here is my stored proc-->
>
>
>CREATE PROCEDURE sp_InsertAirMaster
> @Master nvarchar(20),
> ...
>AS
>INSERT INTO tblMasterAir
> (tblMasterAirwaybill.MAWB
> ...)
>VALUES
> (@Master,
> ...)
>SELECT PK_MasterID FROM tblMaster WHERE PK_MasterID = @@Identity
>
>-->
>The stored proc work well in query analyser but fail in VB...
>
>
>Help...
>Patrice Merineau
You might want to check the names after refreshing. The parameters collection is 0 based and you may be trying to assign values to non-existent parameters. Even though the first parameter (0) is the return value of the sp.
If what you are trying to do is get the identity field value then you can put that in an output parameter. Then the parameter will be in your collection after the sp executes:
CREATE PROCEDURE myProc (@prm1 ... @Identity int OUTPUT) AS
Insert Into myTbl .. Values ..
IF @@ERROR=0
SET @Identity = @@IDENTITY
ELSE
SET @Identity = 0
After the sp executes you can get to the PK like so:
myCmd.Parameters("@Identity")
If this is not a heavily used table you can get away with using the .Refresh command. If it is, you may be taking a big performance hit because the command has to make an extra trip to the server to fill the parameters collection.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement