Beaucoup de requête pour filtrer ?

**jacko842** · 13/01/2012, 18h39

Bonjour,

J'ai un écran pour filtrer un datagridview avec une source de donnée Mysql, j'utilise: 1 textbox, 3 combox-box et 2 datatimepicker pour filtrer mon datagridview le problème c'est que j'utilise beaucoup de requête pour essayer de retourner un résultat valide a chaque combinaisons différentes(combobox, textbox,datatimepicker)

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
 
 If TXT_NOM.Text <> vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE<=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND DATE_ENCODAGE<=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE  PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE  PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE  PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE  PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE  PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE  PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE<=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE  PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "' AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "' AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "' AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "' AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "' AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "' AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE<=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE  DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text = " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE>=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE  DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE<=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND  POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text <> vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE NOM LIKE " & "'" & TXT_NOM.Text & "%'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "' AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE<=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "' AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text = vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE<=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "' AND POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'"
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE<=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "' "
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text = vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text <> vbNullString And DTP_DEBUT.Text = " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE DATE_ENCODAGE<=" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "' AND DESCRIPTION_STATUT=" & "'" & Replace(CB_STAUT.Text, "'", "''") & "' "
                ElseIf TXT_NOM.Text = vbNullString And CB_FONCTION.Text <> vbNullString And CB_PROFIL.Text <> vbNullString And CB_STAUT.Text = vbNullString And DTP_DEBUT.Text <> " " And DTP_FIN.Text <> " " Then
                    str_requete = "SELECT * FROM VUE_CANDIDATURE WHERE POSTE_VACANT=" & "'" & Replace(CB_FONCTION.Text, "'", "''") & "'AND DATE_ENCODAGE BETWEEN" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_DEBUT.Text)) & "'AND" & "'" & String.Format("{0: yyyy/MM/dd}", CDate(DTP_FIN.Text)) & "'AND PROFIL_CANDIDAT=" & "'" & Replace(CB_PROFIL.Text, "'", "''") & "'"
                End If
            Else
                Exit Sub
            End If

Existe t il une autre solution pour remplacer toutes ses requêtes, ou peut être une autre syntaxe sql ?

Je vais essayer demain avec une requête et remplacer les controles par des variables et utiliser le like avec % pour Mysql

D'avance merci

**tchize_** · 14/01/2012, 15h12

Basiquement en pseudo code

Code :

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

1
2
3
4
5
6
7
8
9
requete = "SELECT * FROM VUE_CANDIDATURE WHERE "
ET = ""
si (on a entré un nom)
  requete = requete + "NOM like "+nom
  ET = " AND "
si (on a entré profilCandidat)
  requete = requete + ET + "PROFIL_CANDIDAT like "+profilCandidat
  ET = " AND "
si (etc etc etc)

Beaucoup de requête pour filtrer ?

ADO.NET

Discussions similaires

Partager

Partager