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

**Lynchée** · 17/06/2013, 11h04

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 !

**yinyang** · 17/06/2013, 12h21

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 ?

**Lynchée** · 17/06/2013, 13h06

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

Mon grid est comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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."}

**Lynchée** · 17/06/2013, 15h40

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!

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

ASP.NET

Vue hybride

Discussions similaires

Partager

Partager