I want to use a stored procedure that can make some parameters with null value and then test it if the parameter is null or not and do some coding , the procedure is working fine ... but when i want to use it in gridview in sqldatasource mode assistant it works too .... but wen i run my web page it shows an empty gridview in the first execute the sqldatasource will fill the procedure with just one value wich it : @nomgiac from a session variable
and then when i want to search it fill the procedure with 3 parameters .. just to shiw me an another results
note even if the results is difrrent but it returning the same queries
This is the stored procedure
create proc rechercherGIACetAffiche @nomgiac varchar(20),@nom varchar(30) = null,@par varchar(50) = null
as
begin
IF @nom is null and @par is null
begin
select [ID_Dossier] as 'ID_Dossier'
,[ID_Entreprise] as 'ID_Entreprise'
,[Date_Depot] as 'Date_Dépôt'
,[Type_Etude] as 'Type_Etude'
,[Dernier_Type] as 'Dernier_Type'
,[Eligibile] as 'Eligibilité'
,[Fiche_Information] as 'Fiche_Information'
,[Buletin_Adhesion] as 'Bulletin_d’adhésion'
,[Fiche_Renseignment] as 'Fiche_Renseignment'
,[Attestation] as 'Attestation'
,[Date_Debut] as 'Date_Début'
,[Date_Fin] as 'Date_Fin'
,[ID_Cabinet] as 'ID_Cabinet'
,[Montant_Demander] as 'Montant_Demander'
,[Duree] as 'Durée'
,[Porcentage_Taux] as 'Pourcentage,Taux' from Dossier where Nom_Giac = @nomgiac
return
end
if @par is not null and @nom='CNSS'
begin
select d.[ID_Dossier] as 'ID_Dossier'
,d.[ID_Entreprise] as 'ID_Entreprise'
,[Date_Depot] as 'Date_Dépôt'
,[Type_Etude] as 'Type_Etude'
,[Dernier_Type] as 'Dernier_Type'
,[Eligibile] as 'Eligibilité'
,[Fiche_Information] as 'Fiche_Information'
,[Buletin_Adhesion] as 'Bulletin_d’adhésion'
,[Fiche_Renseignment] as 'Fiche_Renseignment'
,[Attestation] as 'Attestation'
,[Date_Debut] as 'Date_Début'
,[Date_Fin] as 'Date_Fin'
,[ID_Cabinet] as 'ID_Cabinet'
,[Montant_Demander] as 'Montant_Demander'
,[Duree] as 'Durée'
,[Porcentage_Taux] as 'Pourcentage,Taux'
from dbo.Dossier d inner join entreprise e on d.ID_Entreprise=e.ID_Entreprise
where CNSS_Entreprise=@par and d.Nom_Giac=@nomgiac
return
end
else if @par is not null and @nom='RS'
begin
select [ID_Dossier] as 'ID_Dossier'
,[ID_Entreprise] as 'ID_Entreprise'
,[Date_Depot] as 'Date_Dépôt'
,[Type_Etude] as 'Type_Etude'
,[Dernier_Type] as 'Dernier_Type'
,[Eligibile] as 'Eligibilité'
,[Fiche_Information] as 'Fiche_Information'
,[Buletin_Adhesion] as 'Bulletin_d’adhésion'
,[Fiche_Renseignment] as 'Fiche_Renseignment'
,[Attestation] as 'Attestation'
,[Date_Debut] as 'Date_Début'
,[Date_Fin] as 'Date_Fin'
,[ID_Cabinet] as 'ID_Cabinet'
,[Montant_Demander] as 'Montant_Demander'
,[Duree] as 'Durée'
,[Porcentage_Taux] as 'Pourcentage,Taux'
from dbo.Dossier
where Nom_Giac=@nomgiac and ID_Entreprise in( select ID_Entreprise
from dbo.Entreprise
where Raison_Social=@par)
return
end
else if @par is not null and @nom ='Date'
begin
declare @v smalldatetime,@b smalldatetime
set @b=SUBSTRING(@par,1,4)
set @v=SUBSTRING(@par,5,8)
select [ID_Dossier] as 'ID_Dossier'
,[ID_Entreprise] as 'ID_Entreprise'
,[Date_Depot] as 'Date_Dépôt'
,[Type_Etude] as 'Type_Etude'
,[Dernier_Type] as 'Dernier_Type'
,[Eligibile] as 'Eligibilité'
,[Fiche_Information] as 'Fiche_Information'
,[Buletin_Adhesion] as 'Bulletin_d’adhésion'
,[Fiche_Renseignment] as 'Fiche_Renseignment'
,[Attestation] as 'Attestation'
,[Date_Debut] as 'Date_Début'
,[Date_Fin] as 'Date_Fin'
,[ID_Cabinet] as 'ID_Cabinet'
,[Montant_Demander] as 'Montant_Demander'
,[Duree] as 'Durée'
,[Porcentage_Taux] as 'Pourcentage,Taux'
from Dossier
where Date_Depot between @b and @v and Nom_Giac like @nomgiac
return
end
end
and tis is the sqldatasoure code :
<asp:SqlDataSource ID="DOSIIER_RECHERCHE_ET_AFFICHE" runat="server"
ConnectionString="<%$ ConnectionStrings:OfficeConnectionString %>"
SelectCommand="rechercherGIACetAffiche" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:SessionParameter Name="nomgiac" SessionField="Nom_GIAC" Type="String" />
<asp:ControlParameter ControlID="RecherhcerComboBox" DefaultValue="" Name="nom"
PropertyName="SelectedValue" Type="String" />
<asp:SessionParameter DefaultValue="" Name="par" SessionField="Recherche"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
and this is the grid view :
<asp:GridView ID="DossierGV" runat="server"
AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True"
DataKeyNames="ID_Dossier" PageSize="5"
DataSourceID="DOSIIER_RECHERCHE_ET_AFFICHE" >
<Columns>
<asp:BoundField DataField="ID_Dossier" HeaderText="ID_Dossier" ReadOnly="True"
SortExpression="ID_Dossier" />
<asp:BoundField DataField="ID_Entreprise" HeaderText="ID_Entreprise"
SortExpression="ID_Entreprise" />
<asp:BoundField DataField="Date_Dépôt" HeaderText="Date_Dépôt"
SortExpression="Date_Dépôt" />
<asp:BoundField DataField="Type_Etude" HeaderText="Type_Etude"
SortExpression="Type_Etude" />
<asp:BoundField DataField="Dernier_Type" HeaderText="Dernier_Type"
SortExpression="Dernier_Type" />
<asp:BoundField DataField="Eligibilité" HeaderText="Eligibilité"
SortExpression="Eligibilité" />
<asp:BoundField DataField="Fiche_Information" HeaderText="Fiche_Information"
SortExpression="Fiche_Information" />
<asp:BoundField DataField="Bulletin_d’adhésion" HeaderText="Bulletin_d’adhésion"
SortExpression="Bulletin_d’adhésion" />
<asp:BoundField DataField="Fiche_Renseignment" HeaderText="Fiche_Renseignment"
SortExpression="Fiche_Renseignment" />
<asp:BoundField DataField="Attestation" HeaderText="Attestation"
SortExpression="Attestation" />
<asp:BoundField DataField="Date_Début" HeaderText="Date_Début"
SortExpression="Date_Début" />
<asp:BoundField DataField="Date_Fin" HeaderText="Date_Fin"
SortExpression="Date_Fin" />
<asp:BoundField DataField="ID_Cabinet" HeaderText="ID_Cabinet"
SortExpression="ID_Cabinet" />
<asp:BoundField DataField="Montant_Demander" HeaderText="Montant_Demander"
SortExpression="Montant_Demander" />
<asp:BoundField DataField="Durée" HeaderText="Durée" SortExpression="Durée" />
<asp:BoundField DataField="Pourcentage,Taux" HeaderText="Pourcentage,Taux"
SortExpression="Pourcentage,Taux" />
</Columns>
<SelectedRowStyle BackColor="#F2E488" Font-Bold="True" />
</asp:GridView>
