IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
logo
Sommaire > Commande et procédure
        Que veux dire le message d'erreur 'La valeur d'énumération CommandType, 512, n'est pas prise en charge par le fournisseur de données SqlClient .Net Framework' ?
        Que signifie l'erreur 'Un DataReader associé à cette commande est déjà ouvert. Il doit d'abord être fermé.' ?
        Comment gérer les erreurs lorsque j'exécute une procédure stockée ?
        Comment exécuter une procédure stockée ?
        Quelle est la différence entre un paramètre de sortie et un paramètre de retour ?
        Peut-on ajouter une requête stockée dans Access ?



Que veux dire le message d'erreur "La valeur d'énumération CommandType, 512, n'est pas prise en charge par le fournisseur de données SqlClient .Net Framework" ?
auteur : Jean-Marc Rabilloud
Lorsqu'on utilise des objets Command, il peut y avoir nécessité de préciser à quoi réfère le texte passé dans la propriété CommandText. Cela se fait par le biais de la propriété CommandType qui peut prendre les valeurs suivantes :

Constante Valeur Description
Text 1 CommandText correspond à la définition textuelle d'une commande ou d'un appel de procédure stockée.
StoredProc 4 CommandText correspond au nom d'une procédure stockée.
TableDirect 512 CommandText correspond à un nom de table dont les colonnes sont toutes renvoyées.
Cependant, seul l'objet OleDbCommand du fournisseur managé OLEDB accepte la valeur d'énumération TableDirect, ce qui explique l'erreur obtenue. Il est cependant assez facile de créer un scénario contournant le problème.

Imaginons un code utilisant le fournisseur managé SQL Server. devant afficher le contenu de table non connue à la création ; la table sera sélectionnée par un ComboBox, le contenu affiché dans un DataGridView

    Private MaCommande As SqlClient.SqlCommand

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
MaCommande = New SqlClient.SqlCommand
With MaCommande
    .CommandText = "SELECT * FROM information_schema.tables where Table_type='BASE TABLE'"
    .CommandType = CommandType.Text
    .Connection = New SqlClient.SqlConnection("Data Source=ISPCF261025\SQLEXPRESS;" & _
"Initial Catalog=pubs;Integrated Security=True")
    .Connection.Open()
End With
Dim dr As SqlClient.SqlDataReader = MaCommande.ExecuteReader
Do While dr.Read
    Me.TableComboBox.Items.Add(dr.GetString(2))
Loop
dr.Close()
    End Sub

    Private Sub TableComboBox_SelectedIndexChanged(ByVal sender As System.Object, & _
 ByVal e As System.EventArgs) Handles TableComboBox.SelectedIndexChanged
If Me.TableComboBox.SelectedIndex > -1 Then
    Dim MaTable As New DataTable(Me.TableComboBox.Text)
    MaCommande.CommandText = "SELECT * FROM " + Me.TableComboBox.Text
    MaTable.Load(MaCommande.ExecuteReader()) 
    Me.TableDataGridView.DataSource = MaTable
End If
    End Sub
Si ce code est fonctionnel, c'est une horreur en termes de pratique de codage et de sécurité. A lire sur le sujet :

lien : en http://www.sommarskog.se/dynamic_sql.html#objectnames

Que signifie l'erreur "Un DataReader associé à cette commande est déjà ouvert. Il doit d'abord être fermé." ?
auteur : Jean-Marc Rabilloud
L'objet DataReader fonctionne en mode connecté, il est exclusif sur sa connexion. Cela veut dire Que sur une même connexion, il ne peut y avoir plus d'un DataReader ouvert en même temps. Vous devez soit fermer un des DataReader, soit utiliser deux connexions


Comment gérer les erreurs lorsque j'exécute une procédure stockée ?
auteur : Jean-Marc Rabilloud
Cela va dépendre principalement de comment sont rédigées les procédures stockées. Il s'agit toutefois d'un sujet assez complexe qui demanderait une étude beaucoup plus longue qu'une réponse dans cette FAQ.

Pour faire simple, on travaille soit sur l'interception des erreurs et/ou des messages de la connexion, soit en interprétant les paramètres renvoyés par la procédure.

Commençons par la gestion des erreurs sur la connexion. Imaginons la procédure stockée suivante :

CREATE proc TestRaiseError 
@Titre nvarchar (6),
@Magasin nvarchar (4)
AS
DECLARE @err_message nvarchar(255)

--if le magasin n'existe pas lever une erreur de sévérité >10
IF NOT EXISTS (Select stor_id from sales where stor_id = @Magasin) 
BEGIN
SET @err_message = 'Magasin id : ' + @Magasin + ' Introuvable'
RAISERROR (@err_message, 11,1)
END

--si le titre existe mais pas dans le magasin, lever une erreur de sévérité <10
IF EXISTS (Select title_id from sales where title_id = @Titre and stor_id = @Magasin)
BEGIN
Select * from sales where title_id = @Titre and stor_id = @Magasin
END
ELSE
BEGIN
IF EXISTS (select title_id from sales where title_id = @Titre)
BEGIN
SET @err_message = 'le titre ' + @Titre +  ' n''a pas été vendu dans ce magasin'
    RAISERROR (@err_message,9, 1) 
END
ELSE
BEGIN
SET @err_message = 'le titre ' + @Titre +  ' n''a pas été vendu'
    RAISERROR (@err_message,10, 1) 
END
END
Celle-ci va générer des erreurs différentes avec des sévérités différentes selon les cas.

Pour intercepter ces erreurs, nous allons utiliser l'objet SQLException.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Using MaConn As New SqlClient.SqlConnection("Data Source=ISPCF261025\SQLEXPRESS;" & _
"Initial Catalog=pubs;Integrated Security=True")
    AddHandler MaConn.InfoMessage, AddressOf MaConn_InfoMessage
    MaConn.Open()
    Try
Dim cmd As New SqlClient.SqlCommand("TestRaiseError")
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = MaConn
cmd.Parameters.Add(New SqlClient.SqlParameter("@Magasin", SqlDbType.NVarChar, 4))
cmd.Parameters.Add(New SqlClient.SqlParameter("@Titre", SqlDbType.NVarChar, 6))
cmd.Parameters("@Magasin").Value = "6380"
cmd.Parameters("@Titre").Value = "PS2091"
MessageBox.Show(CStr(cmd.ExecuteScalar))
cmd.Parameters("@Magasin").Value = "6380"
cmd.Parameters("@Titre").Value = "PS2090"
MessageBox.Show(CStr(cmd.ExecuteScalar))
cmd.Parameters("@Magasin").Value = "6080"
cmd.Parameters("@Titre").Value = "PS2091"
MessageBox.Show(CStr(cmd.ExecuteScalar))
    Catch SqlEx As SqlClient.SqlException
Dim myError As SqlClient.SqlError
MessageBox.Show("Errors Count:" + SqlEx.Errors.Count)
For Each myError In SqlEx.Errors
    MessageBox.Show(myError.Number + " - " + myError.Message)
Next
    End Try
    MaConn.Close()
End Using

End Sub

Public Sub MaConn_InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)
MessageBox.Show("info message event: " + e.Message)
End Sub
Dans cet exemple, nous allons traiter à la fois l'évènement InfoMessage puisque certaine sévérité sont inférieure à 10 et SQLException pour les sévérités supérieures.

Lors de l'exécution, nous allons donc avoir soit l'affichage de la quantité, soit affichage des messages d'erreurs.

Travaillons maintenant par récupération de paramètre. Généralement on utilise le paramètre @@ERROR dans SQL Server. Donc, imaginons la procédure stockée suivante :

USE PUBS
GO

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE TestParamError
@ID varchar(11), @Nom varchar(40), @Prenom varchar(20), @Telephone varchar(12), @Contrat bit
AS
DECLARE @pERROR int


SET NOCOUNT ON

BEGIN TRAN 
    INSERT INTO Authors (au_id, au_fname, au_lname, phone, contract)
 VALUES (@ID, @Nom, @Prenom, @Telephone, @Contrat)

    SELECT @pERROR = @@ERROR
    IF @pERROR != 0 GOTO HANDLE_ERROR

    COMMIT TRAN

    RETURN 0

HANDLE_ERROR:
    ROLLBACK TRAN
    RETURN @pERROR
GO
On va alors pouvoir accéder à l'erreur en interceptant l'erreur lors de l'exécution de la commande puis en lisant le paramètre de retour pERROR, comme dans le cas suivant si vous cliquez deux fois de suite sur le bouton :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Using MaConn As New SqlClient.SqlConnection("Data Source=FIXE;Initial Catalog=pubs;" & _
"Integrated Security=True")
    MaConn.Open()
    Dim Command As New SqlClient.SqlCommand("TestParamError")
    Try
Command.CommandType = CommandType.StoredProcedure
Command.Connection = MaConn
With Command
    .Parameters.AddWithValue("@ID", "123-45-6789")
    .Parameters.AddWithValue("@Nom", "rabilloud")
    .Parameters.AddWithValue("@Prenom", "jean-marc")
    .Parameters.AddWithValue("@Telephone", "0102030405")
    .Parameters.AddWithValue("@Contrat", 1)
    .Parameters.Add("@pError", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
End With
Command.ExecuteNonQuery()
    Catch ex As Exception
If CInt(Command.Parameters("@pError").Value) > 0 Then
    MessageBox.Show("Erreur " + CInt(Command.Parameters("@pError").Value).ToString + & _
" - Echec de la transaction")
Else
    MessageBox.Show("Erreur hors procédure")
End If
    End Try
    MaConn.Close()
End Using

End Sub

Comment exécuter une procédure stockée ?
auteur : Jean-Marc Rabilloud
C'est relativement simple. On part toujours d'un objet command. On place le nom de la procédure stockée dans la propriété CommandText et on valorise la propriété CommandType sur 'StoredProcedure'. On gère alors les différents paramètres. L'exécution de la commande déclenchera alors l'exécution de la procédure stockée.

Une bonne connaissance de celle-ci est parfois nécessaire pour gérer les réponses complexes. En effet, une procédure peut renvoyer plusieurs résultats différents sous la forme de paramètre ou de jeu d'enregistrements.

Prenons la procédure suivante :

Create Procedure ProcedureStockee
@auteur varchar (11),
@nbtitre Integer OUTPUT
As

select authors.au_fname, authors.au_lname, titles.title 
from authors join titleauthor on
authors.au_id=titleauthor.au_id
join titles
on titles.title_id=titleauthor.title_id
where authors.au_id=@auteur
set @nbtitre = @@Rowcount
return (@@error)
Elle utilise un paramètre d'entrée (l'identifiant de l'auteur), un paramètre de sortie (le nombre de livre écrit par l'auteur), un paramètre de retour (la valeur d'erreur) et éventuellement le jeu d'enregistrement correspondant à la requête. On pourrait exploiter cette procédure avec le code suivant :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Using MaConn As New SqlClient.SqlConnection("Data Source=ISPCF261025\SQLEXPRESS;" & _
"Initial Catalog=pubs;Integrated Security=True")
    MaConn.Open()
    Dim Command As New SqlClient.SqlCommand("ProcedureStockee")
    Try
Command.CommandType = CommandType.StoredProcedure
Command.Connection = MaConn
With Command
    .Parameters.AddWithValue("@auteur", Me.AuteurTextbox.Text)
    .Parameters.Add("@nbtitre", SqlDbType.Int).Direction = ParameterDirection.Output
    .Parameters.Add("@ValRet", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
End With
Dim MonDr As SqlClient.SqlDataReader = Command.ExecuteReader
Dim TblTitreAuteur As New DataTable
TblTitreAuteur.Load(MonDr)
Me.DataGridView1.DataSource = TblTitreAuteur
Me.NombreTitreLabel.Text = Command.Parameters("@NbTitre").Value.ToString
    Catch ex As Exception
If Command.Parameters("@ValRet").Value Is Nothing Then
    MessageBox.Show("La procédure à renvoyée une erreur")
End If
    End Try
    MaConn.Close()
End Using
End Sub

Quelle est la différence entre un paramètre de sortie et un paramètre de retour ?
auteur : Jean-Marc Rabilloud
Un paramètre de retour est renvoyé par la commande Return de la procédure stockée, un paramètre de sortie est déclaré comme tel dans la procédure. Une procédure peut utiliser simultanément les deux types de paramètre. Pour fonctionner, le code ADO.NET doit utiliser la bonne définition des paramètres. Par exemple, pour exploiter la procédure stockée suivante

Create Procedure VenteType
@type VARCHAR(55),
@Qte_total INT OUTPUT, @Qte_moy INT OUTPUT
AS
IF @type IS NULL
BEGIN  PRINT 'type is required'  RETURN (4)
END
SELECT @Qte_total=SUM(qty), @Qte_moy = AVG(qty) FROM sales
join titles on
sales.title_id = titles.title_id
WHERE titles.type = @type
IF @Qte_total IS NULL 
AND     @Qte_moy IS NULL
BEGIN
RETURN (3) -- total et moyenne Null
END
IF @Qte_moy IS NULL 
BEGIN 
RETURN (1) -- moyenne null
END
IF @Qte_total IS NULL
BEGIN
RETURN (2) -- total null

END
Nous pourrions avoir un code comme :

Using MaConn As New SqlClient.SqlConnection("Data Source=ISPCF261025\SQLEXPRESS;" & _
"Initial Catalog=pubs;Integrated Security=True")
    MaConn.Open()
    Dim Command As New SqlClient.SqlCommand("VenteType")
    Try
Command.CommandType = CommandType.StoredProcedure
Command.Connection = MaConn
With Command
    .Parameters.AddWithValue("@type", Me.TypeTextbox.Text)
    .Parameters.Add("@Qte_total", SqlDbType.Int).Direction = ParameterDirection.Output
    .Parameters.Add("@Qte_moy", SqlDbType.Int).Direction = ParameterDirection.Output
    .Parameters.Add("@ValRet", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
End With
Command.ExecuteNonQuery()
Select Case CInt(Command.Parameters("@ValRet").Value)
    Case 0
MessageBox.Show("Quantité totale : " + CInt(Command.Parameters("@Qte_total").Value).ToString & _
 + vbCrLf + "Quantité moyenne : " + CInt(Command.Parameters("@qte_moy").Value).ToString)

    Case 1
MessageBox.Show("Impossible de calculer la moyenne")

    Case 2
MessageBox.Show("Impossible de calculer le total")

    Case 3
MessageBox.Show("Impossible de calculer la moyenne et le total")

    Case 4
MessageBox.Show("Type manquant")
End Select
    Catch ex As Exception
MessageBox.Show("Erreur")
    End Try
    MaConn.Close()
End Using
Notez que le nom donné au paramètre de retour donné dans le code n'existe pas dans la procédure stockée. Cependant celui-ci mappe bien la valeur retournée par la procédure stockée.


Peut-on ajouter une requête stockée dans Access ?
auteur : Jean-Marc Rabilloud
On peut ajouter des requêtes stockées dans une base Access soit à l'aide de la bibliothèque ADOX, soit en utilisant la requête DDL "CREATE PROCEDURE".

L'exemple suivant crée deux requêtes une non paramétrée et une paramétrée dans la base Access Northwind.mdb :

Using MaConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\User\tutos\NorthWind.mdb")
    'je crée une requête non paramétrée dans access
    MaConn.Open()
    Dim Command As New OleDb.OleDbCommand()
    Command.CommandText = "CREATE PROCEDURE SelectEmployees AS SELECT * FROM Employees"
    Command.CommandType = CommandType.Text
    Command.Connection = MaConn
    Command.ExecuteNonQuery()
    'j'appelle la nouvelle requête access
    Command.CommandText = "SelectEmployees"
    Command.CommandType = CommandType.StoredProcedure
    Dim Matable As New DataTable
    Matable.Load(Command.ExecuteReader)
    MsgBox(Matable.Rows.Count)
    'je crée une procedure stockée paramétrée
    Command.CommandText = "CREATE PROCEDURE SelectEmployee AS PARAMETERS EmpId  INTEGER;" & _
" SELECT Employees.* FROM Employees WHERE EmployeeID=EmpId"
    Command.CommandType = CommandType.Text
    Command.ExecuteNonQuery()
    'j'appelle la nouvelle requête access
    Command.CommandText = "SelectEmployee"
    Command.CommandType = CommandType.StoredProcedure
    Command.Parameters.Add("@EmpId", OleDb.OleDbType.Integer).Value = 2
    Dim MonDr As OleDb.OleDbDataReader = Command.ExecuteReader
    If MonDr.HasRows Then
MonDr.Read()
MsgBox(MonDr.GetString(MonDr.GetOrdinal("Lastname")))
    End If
End Using


Consultez les autres F.A.Q's


Valid XHTML 1.1!Valid CSS!

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2009 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.