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 !

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

ASP.NET

Mode arborescent

Discussions similaires

Partager

Partager