Procédure stockée & Affichage des données

Bonjour,
Après maintes consultations fructueuses sur votre forum, je colle sur un problème assez particulier.

En effet, afin d'afficher mes résultats dans un GridView, j'ai une procédure stockée qui requête la base de donnée.

Quand je teste ma procédure du côté Sql, avec mes paramètres, tout marche correctement.
Afin d'implémenter cette procédure, je fais un update de mon modèle, je récupère ma procédure stockée et je l'importe avec ' Add Function Import'
Le type de retour est une vue prennant l'ensemble des Messages contenu dans la base de donnée.

Mon problème:
Quand j'affiche ma liste de resultat dans le GridView, cela retourne le nombre exact de résultat, mais en dupliquant le premier message de la liste.
Alors que, la même procédure stockée avec les même paramètre du côté sql, retourne la vraie liste de résultat.

Voici quelques éléments pouvant aider au débogage:

Ma Procedure stockée :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   ALTER PROCEDURE [dbo].[GetNewMessages] @champ1 nvarchar(4000) = null, @value1 nvarchar(4000) = null, @champ2 nvarchar(4000) = null, @value2 nvarchar(4000) = null, @champ3 nvarchar(4000) = null, @value3 nvarchar(4000) = null, @agregateur nvarchar(4000) = null, @agregateur2 nvarchar(4000) = null, @operande1 nvarchar(4000) = null, @operande2 nvarchar(4000) = null, @operande3 nvarchar(4000) = null, @typeMessage nvarchar(4000) = null, @DateJour varchar(40)=null, @DateDebut varchar(40)=null, @DateFin varchar(40)=null   AS BEGIN   declare @requete as nvarchar(4000)   set @requete='SELECT tm1.libelleTypeM, tm1.sens, tm1.system, m.timestamp, tcc.libelle AS CaracMessage, cc.value AS valeurCarac, m.chemin FROM dbo.Messages AS m INNER JOIN dbo.TypeMessage AS tmc ON m.idTypeMessage = tmc.idTypeMessage INNER JOIN dbo.Champs AS c1 ON m.idMessage = c1.idMessage INNER JOIN dbo.Champs AS c2 ON m.idMessage = c2.idMessage INNER JOIN dbo.Champs AS c3 ON m.idMessage = c3.idMessage INNER JOIN dbo.Champs AS cc ON m.idMessage = cc.idMessage INNER JOIN dbo.TypeChamp AS tcc ON cc.idTypeChamp = tcc.idTypeChamp AND tmc.CaracMessage = tcc.idTypeChamp INNER JOIN dbo.TypeChamp AS tc1 ON c1.idTypeChamp = tc1.idTypeChamp INNER JOIN dbo.TypeChamp AS tc2 ON c2.idTypeChamp = tc2.idTypeChamp INNER JOIN dbo.TypeChamp AS tc3 ON c3.idTypeChamp = tc3.idTypeChamp INNER JOIN dbo.TypeMessage AS tm1 ON m.idTypeMessage = tm1.idTypeMessage INNER JOIN dbo.TypeMessage AS tm2 ON m.idTypeMessage = tm2.idTypeMessage AND tc2.idTypeChamp = tm2.CaracMessage INNER JOIN dbo.TypeMessage AS tm3 ON m.idTypeMessage = tm3.idTypeMessage AND tc3.idTypeChamp = tm3.CaracMessage '   if(@operande1='LIKE') set @value1 = '%'+@value1+'%'   if(@operande2='LIKE') set @value2 = '%'+@value2+'%'   if(@operande3='LIKE') set @value3 = '%'+@value3+'%'       if((@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null)and (@champ3 is not null) and (@value3 is not null) and (@typeMessage is not null) and (@DateJour is not null)) set @requete = @requete + ' WHERE (tc.libelle='''+ @champ1 +''' AND c.value '+@operande1+''''+@value1 +''' AND tm.libelleTypeM='''+@typeMessage+''') '+@agregateur+' (tc2.libelle ='''+ @champ2 + ''' AND c2.value '+@operande2+''''+@value2+''' AND tm2.libelleTypeM='''+@typeMessage+''') '+@agregateur2 +' (tc3.libelle ='''+@champ3+''' AND c3.value '+@operande3+''''+@value3+''' AND tm3.libelleTypeM='''+@typeMessage+''') AND (CAST(m.timestamp as DATETIME)= (CONVERT(DATETIME,'''+@DateJour+''',120))) ' else if((@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null)and (@champ3 is not null) and (@value3 is not null) and (@typeMessage is not null) and (@DateDebut is not null) and (@DateFin is not null)) set @requete = @requete + ' WHERE (tc.libelle='''+ @champ1 +''' AND c.value '+@operande1+''''+@value1 +''' AND tm.libelleTypeM='''+@typeMessage+''') '+@agregateur+' (tc2.libelle ='''+ @champ2 + ''' AND c2.value '+@operande2+''''+@value2+''' AND tm2.libelleTypeM='''+@typeMessage+''') '+@agregateur2 +' (tc3.libelle ='''+@champ3+''' AND c3.value '+@operande3+''''+@value3+''' AND tm3.libelleTypeM='''+@typeMessage+''') AND (CAST(m.timestamp as DATETIME)>(CONVERT(DATETIME,'''+@DateDebut+''',120)) AND CAST(m.timestamp as DATETIME)<(CONVERT(DATETIME,'''+@DateFin+''',120)) ) ' else if((@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null)and (@champ3 is not null) and (@value3 is not null) and (@typeMessage is not null)) set @requete = @requete + ' WHERE (tc.libelle='''+ @champ1 +''' AND c.value '+@operande1+''''+@value1 +''' AND tm.libelleTypeM='''+@typeMessage+''') '+@agregateur+' (tc2.libelle ='''+ @champ2 + ''' AND c2.value '+@operande2+''''+@value2+''' AND tm2.libelleTypeM='''+@typeMessage+''') '+@agregateur2 +' (tc3.libelle ='''+@champ3+''' AND c3.value '+@operande3+''''+@value3+''' AND tm3.libelleTypeM='''+@typeMessage+''') ' else if((@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null) and (@champ3 is not null) and (@value3 is not null) and (@DateJour is not null)) set @requete = @requete + ' WHERE (tc.libelle='''+ @champ1 +''' AND c.value '+@operande1+''''+@value1 +''') '+@agregateur+' (tc2.libelle ='''+ @champ2 + ''' AND c2.value '+@operande2+''''+@value2+''') '+@agregateur2 +' (tc3.libelle ='''+@champ3+''' AND c3.value '+@operande3+''''+@value3+''') AND (CAST(m.timestamp as DATETIME)= (CONVERT(DATETIME,'''+@DateJour+''',120))) ' else if((@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null) and (@champ3 is not null) and (@value3 is not null) and (@DateDebut is not null) and (@DateFin is not null)) set @requete = @requete + ' WHERE (tc.libelle='''+ @champ1 +''' AND c.value '+@operande1+''''+@value1 +''') '+@agregateur+' (tc2.libelle ='''+ @champ2 + ''' AND c2.value '+@operande2+''''+@value2+''') '+@agregateur2 +' (tc3.libelle ='''+@champ3+''' AND c3.value '+@operande3+''''+@value3+''') AND (CAST(m.timestamp as DATETIME)>(CONVERT(DATETIME,'''+@DateDebut+''',120)) AND CAST(m.timestamp as DATETIME)<(CONVERT(DATETIME,'''+@DateFin+''',120)) ) ' else if((@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null) and (@champ3 is not null) and (@value3 is not null))   set @requete = @requete + ' WHERE (tc.libelle='''+ @champ1 +''' AND c.value '+@operande1+''''+@value1 +''') '+@agregateur+' (tc2.libelle ='''+ @champ2 + ''' AND c2.value '+@operande2+''''+@value2+''') '+@agregateur2 +' (tc3.libelle ='''+@champ3+''' AND c3.value '+@operande3+''''+@value3+''') '   else if(@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null)and (@typeMessage is not null) and (@DateJour is not null) set @requete = @requete +' WHERE (tc.libelle='''+ @champ1 + ''' AND c.value '+@operande1+''''+ @value1+''' AND tm.libelleTypeM='''+@typeMessage+''' ) '+@agregateur+' (tc2.libelle='''+@champ2+''' AND c2.value '+@operande2+''''+@value2+''' AND tm2.libelleTypeM='''+@typeMessage+''') AND (CAST(m.timestamp as DATETIME)= (CONVERT(DATETIME,'''+@DateJour+''',120))) ' else if(@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null)and (@typeMessage is not null) and (@DateDebut is not null) and (@DateFin is not null) set @requete = @requete +' WHERE (tc.libelle='''+ @champ1 + ''' AND c.value '+@operande1+''''+ @value1+''' AND tm.libelleTypeM='''+@typeMessage+''' ) '+@agregateur+' (tc2.libelle='''+@champ2+''' AND c2.value '+@operande2+''''+@value2+''' AND tm2.libelleTypeM='''+@typeMessage+''') AND (CAST(m.timestamp as DATETIME)>(CONVERT(DATETIME,'''+@DateDebut+''',120)) AND CAST(m.timestamp as DATETIME)<(CONVERT(DATETIME,'''+@DateFin+''',120)) ) ' else if(@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null)and (@typeMessage is not null)   set @requete = @requete +' WHERE (tc.libelle='''+ @champ1 + ''' AND c.value '+@operande1+''''+ @value1+''' AND tm.libelleTypeM='''+@typeMessage+''' ) '+@agregateur+' (tc2.libelle='''+@champ2+''' AND c2.value '+@operande2+''''+@value2+''' AND tm2.libelleTypeM='''+@typeMessage+''') ' else if(@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null) set @requete = @requete +' WHERE (tc.libelle='''+ @champ1 + ''' AND c.value '+@operande1+''''+ @value1+''' ) '+@agregateur+' (tc2.libelle='''+@champ2+''' AND c2.value '+@operande2+''''+@value2+''') AND (CAST(m.timestamp as DATETIME)= (CONVERT(DATETIME,'''+@DateJour+''',120))) ' else if(@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null) set @requete = @requete +' WHERE (tc.libelle='''+ @champ1 + ''' AND c.value '+@operande1+''''+ @value1+''' ) '+@agregateur+' (tc2.libelle='''+@champ2+''' AND c2.value '+@operande2+''''+@value2+''') AND (CAST(m.timestamp as DATETIME)>(CONVERT(DATETIME,'''+@DateDebut+''',120)) AND CAST(m.timestamp as DATETIME)<(CONVERT(DATETIME,'''+@DateFin+''',120)) ) '   else if(@champ1 is not null) and (@value1 is not null) and (@champ2 is not null) and (@value2 is not null)   set @requete = @requete +' WHERE (tc.libelle='''+ @champ1 + ''' AND c.value '+@operande1+''''+ @value1+''' ) '+@agregateur+' (tc2.libelle='''+@champ2+''' AND c2.value '+@operande2+''''+@value2+''')'   else if(@champ1 is not null) and (@value1 is not null) and (@typeMessage is not null) and (@DateJour is not null) set @requete = @requete +' WHERE (tc1.libelle='''+@champ1+''' AND c1.value '+@operande1+''''+@value1+''' AND tm1.libelleTypeM='''+@typeMessage+''') AND (CAST(m.timestamp as DATETIME)= (CONVERT(DATETIME,'''+@DateJour+''',120))) ' else if(@champ1 is not null) and (@value1 is not null) and (@typeMessage is not null) and (@DateDebut is not null) and (@DateFin is not null) set @requete = @requete +' WHERE (tc1.libelle='''+@champ1+''' AND c1.value '+@operande1+''''+@value1+''' AND tm1.libelleTypeM='''+@typeMessage+''') AND (CAST(m.timestamp as DATETIME)>(CONVERT(DATETIME,'''+@DateDebut+''',120)) AND CAST(m.timestamp as DATETIME)<(CONVERT(DATETIME,'''+@DateFin+''',120)) ) ' else if(@champ1 is not null) and (@value1 is not null) and (@typeMessage is not null) set @requete = @requete +' WHERE (tc1.libelle='''+@champ1+''' AND c1.value '+@operande1+''''+@value1+''' AND tm1.libelleTypeM='''+@typeMessage+''') ' else if(@champ1 is not null) and (@value1 is not null) and (@DateJour is not null) set @requete = @requete +' WHERE (tc1.libelle='''+@champ1+''' AND c1.value '+@operande1+''''+@value1+''') AND (CAST(m.timestamp as DATETIME)= (CONVERT(DATETIME,'''+@DateJour+''',120))) ' else if(@champ1 is not null) and (@value1 is not null) and (@DateDebut is not null) and (@DateFin is not null) set @requete = @requete +' WHERE (tc1.libelle='''+@champ1+''' AND c1.value '+@operande1+''''+@value1+''') AND (CAST(m.timestamp as DATETIME)>=(CONVERT(DATETIME,'''+@DateDebut+''',120)) AND CAST(m.timestamp as DATETIME)<=(CONVERT(DATETIME,'''+@DateFin+''',120)) ) ' else set @requete = @requete +' WHERE (tc1.libelle='''+@champ1+''' AND c1.value '+@operande1+''''+@value1+''') '   exec sp_executesql @requete END

---

Et la façon dont j'affiche les résultats dans mon GridView :

Code:

---

1 2 3

ObjectResult<ViewAllMessages> lstMessage = mon_modele.GetNewMessages(ddlChamps.SelectedValue, ValueChamp.Text, null, null, null, null, null, null, ddlOperateur.SelectedText, null, null, null, null, null, null); RadGridTest.DataSource = lstMessage; RadGridTest.DataBind();

---

Ma mission se terminant en fin de semaine, je dois absolument trouver l'erreur :/
Je vous remercie d'avance de votre aide !

Bonjour,

Qu'est-ce que te donne Visual Studio quand tu "affiches" ta liste "lstMessage" en mode débugage (la petite loupe que ton curseur de souris est dessus) ?

Peux-tu nous fournir la partie ASPX de ton gridview ?

Alors quand mon curseur est sur lstMessage, j'obtiens son type :
{ System.Data.Objects.ObjectResult<DataAccess.ViewAllMessages> }


Mon grid est comme ceci :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

<telerik:RadGrid ID="RadGridTest" runat="server" CssClass="Gridcss" AllowSorting="true" AllowPaging="true" PageSize="5" OnNeedDataSource="RadGridTest_NeedDataSource" Skin="Default" AutoGenerateColumns="false"> <ExportSettings HideStructureColumns="false" ExportOnlyData="true" IgnorePaging="false" OpenInNewWindow="true"> </ExportSettings> <SortingSettings EnableSkinSortStyles="true" SortedBackColor="Silver" /> <MasterTableView Width="100%" CommandItemDisplay="Top" AllowCustomPaging="false"> <Columns> <telerik:GridBoundColumn DataField="libelleTypeM" HeaderText="libelleTypeMessage" SortExpression="libelleTypeM"> </telerik:GridBoundColumn> <telerik:GridBoundColumn DataField="timestamp" HeaderText="timestamp"> </telerik:GridBoundColumn> <telerik:GridBoundColumn DataField="sens" HeaderText="sens" SortExpression="Sens"> </telerik:GridBoundColumn> <telerik:GridBoundColumn DataField="system" HeaderText="system" SortExpression="System"> </telerik:GridBoundColumn> <telerik:GridBoundColumn DataField="CaracMessage" HeaderText="CaracMessage" SortExpression="caracteristiqueMessage"> </telerik:GridBoundColumn> <telerik:GridBoundColumn DataField="valeurCarac" HeaderText="valeur" SortExpression="valeurCaracteristique"> </telerik:GridBoundColumn>   <telerik:GridHyperLinkColumn DataNavigateUrlFields="chemin" DataTextField="chemin" HeaderText="chemin"></telerik:GridHyperLinkColumn> </Columns> <CommandItemSettings ShowExportToExcelButton="true" ShowAddNewRecordButton="false"> </CommandItemSettings> <RowIndicatorColumn FilterControlAltText="Filter RowIndicator column" Visible="True"> </RowIndicatorColumn> <ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column" Visible="True"> </ExpandCollapseColumn> <EditFormSettings> <EditColumn FilterControlAltText="Filter EditCommandColumn column"> </EditColumn> </EditFormSettings> <PagerStyle PageSizeControlType="RadComboBox" /> <ItemStyle BackColor="#DFDFDF" /> <HeaderStyle BackColor="#DFDFDF" ForeColor="#057294" /> <AlternatingItemStyle BackColor="#057294" ForeColor="white" /> </MasterTableView> <PagerStyle PageSizeControlType="RadComboBox" /> </telerik:RadGrid>

---

Par contre, en mode debug, quelque chose attire mon attention quand on déplie les informations sur lstMessage.
Dans base -> ResultView base , j'ai cette exception :{System.SystemException} = {"The result of a query cannot be enumerated more than once."}

J'ai résolu mon problème.
Comme je travaillais avec Linq to Entities, mon modèle demandait une clé primaire pour ma vue.
Ma clef n'était pas unique et faussait le résultat de ma fonction.
J'ai résolu ce problème en mettant tous mes attributs en clé primaire.

Je vous remercie de votre intérêt!