User-1939834628 postedI'm working on a ASP.NET form that should populate a GridView trough a SQLDataSource using controls values (TextBoxes, DropDownList) to set SelectParameters values. The ASP code below depict the most relevant controls or (at least those I think they are related with the issue I'm trying to fix). The SQL backend is a MySQL Server 5.6 (I can test this also on MySQL 8.0 anyway).
<asp:TextBox ID="_txtConvenzionato" runat="server" ClientIDMode="Static" />
<asp:DropDownList
ClientIDMode="Static"
ID="_ddlVeicoli"
DataSourceID="_sdsVeicoli"
DataTextField ="targa"
DataValueField ="id"
runat="server"
AppendDataBoundItems="true">
<asp:ListItem Text="TUTTI" Value="" Selected="True" />
</asp:DropDownList>
<asp:DropDownList
ClientIDMode="Static"
ID="_ddlMese"
runat="server" >
<asp:ListItem Value="1" Text="Gennaio" />
<asp:ListItem Value="2" Text="Febbraio" />
<asp:ListItem Value="3" Text="Marzo" />
<asp:ListItem Value="4" Text="Aprile" />
<asp:ListItem Value="5" Text="Maggio" />
<asp:ListItem Value="6" Text="Giugno" />
<asp:ListItem Value="7" Text="Luglio" />
<asp:ListItem Value="8" Text="Agosto" />
<asp:ListItem Value="9" Text="Settembre" />
<asp:ListItem Value="10" Text="Ottobre" />
<asp:ListItem Value="11" Text="Novembre" />
<asp:ListItem Value="12" Text="Dicembre" />
</asp:DropDownList>
<asp:Button ID="_btnRicerca" runat="server" ClientIDMode="Static" OnClick="_btnRicerca_Click" Text="Ricerca" />
<asp:GridView
ID="_gvNoleggi"
DataSourceID="_sdsNoleggi"
runat="server"
AutoGenerateColumns="false"
AllowSorting="true"
AllowPaging="true"
EmptyDataText="Nessun noleggio presente." >
<Columns>
<asp:BoundField DataField="numero_noleggio" HeaderText="Numero" SortExpression="numero_noleggio" />
<asp:BoundField DataField="modalita" HeaderText="Modalità" />
<asp:BoundField DataField="durata" HeaderText="Durata (gg)" SortExpression="durata" />
<asp:BoundField DataField="targa" HeaderText="Targa" SortExpression="targa" />
<asp:TemplateField HeaderText="Canone">
<ItemTemplate>
<%# Eval("canone") %> €
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="ragione_sociale" HeaderText="Convenzionato" SortExpression="ragione_sociale" />
<asp:BoundField DataField="mese" HeaderText="Mese" />
</Columns>
</asp:GridView>
I have two SQLDataSource the first one feeds _ddlVeicoli , the second one feeds _gvNoleggi.
<asp:SqlDataSource
ID="_sdsVeicoli"
runat="server"
ConnectionString="<%$ ConnectionStrings:sos_db %>"
ProviderName="<%$ ConnectionStrings:sos_db.ProviderName %>"
OnSelecting="_sdsVeicoli_Selecting"
SelectCommand="
SELECT
id,
targa
FROM
veicoli_contratti
WHERE
data_cancellazione IS NULL
AND stato = 'OPERATIVA'
AND targa IS NOT NULL
AND NOT(targa = '')
AND ((@id_convenzionato IS NULL) OR (id_convenzionato = @id_convenzionato))
ORDER BY targa">
<SelectParameters>
<asp:Parameter
Direction="Input"
ConvertEmptyStringToNull="true"
DbType="Int32"
DefaultValue=""
Name="id_convenzionato" />
<asp:SqlDataSource
ID="_sdsNoleggi"
runat="server"
EnableCaching="false"
CancelSelectOnNullParameter="false"
ConnectionString="<%$ ConnectionStrings:sos_db %>"
ProviderName="<%$ ConnectionStrings:sos_db.ProviderName %>"
OnSelecting="_sdsNoleggi_Selecting"
SelectCommand="
SELECT
noleggio_veicoli.id AS id_noleggio,
IF((noleggio_veicoli.serie IS NULL) OR (noleggio_veicoli.serie = ''), numero, CONCAT(numero,'/',noleggio_veicoli.serie)) AS numero_noleggio,
numero,
noleggio_veicoli.serie AS serie,
IF(giorni IS NULL, giorni_presunti, giorni) AS durata,
veicoli_contratti.targa AS targa,
veicoli_contratti.canone_noleggio AS canone,
anag_convenzionati.nome AS ragione_sociale,
tab_modalita_noleggio.descrizione AS modalita,
MONTH(noleggio_veicoli.rientro_data) AS mese
FROM
noleggio_veicoli
INNER JOIN
veicoli_contratti ON veicoli_contratti.id = noleggio_veicoli.id_veicolo
INNER JOIN
anag_convenzionati ON anag_convenzionati.id = noleggio_veicoli.id_convenzionato
INNER JOIN
tab_modalita_noleggio ON tab_modalita_noleggio.modalita_noleggio = noleggio_veicoli.modalita_noleggio
WHERE
(noleggio_veicoli.data_cancellazione IS NULL)
AND (veicoli_contratti.data_cancellazione IS NULL)
AND (anag_convenzionati.data_cancellazione IS NULL)
AND (veicoli_contratti.stato = 'OPERATIVA')
AND ((@vip = 'False') OR (vip = 1))
AND ((@mese IS NULL) OR (MONTH(rientro_data) = @mese))
AND ((@anno IS NULL) OR (YEAR(rientro_data)= @anno))
AND ((@id_veicolo IS NULL) OR (noleggio_veicoli.id_veicolo = @id_veicolo))
AND ((@serie IS NULL) OR (noleggio_veicoli.serie = @serie))
AND ((@modalita_noleggio IS NULL) OR (noleggio_veicoli.modalita_noleggio LIKE CONCAT('%',@modalita_noleggio,'%')))
AND ((@id_convenzionato IS NULL) OR (noleggio_veicoli.id_convenzionato = @id_convenzionato));">
<SelectParameters>
<asp:Parameter
Direction="Input"
ConvertEmptyStringToNull="true"
DbType="Int32"
DefaultValue=""
Name="mese" />
<asp:Parameter
Direction="Input"
ConvertEmptyStringToNull="true"
DbType="Int32"
DefaultValue=""
Name="anno" />
<asp:Parameter
Direction="Input"
ConvertEmptyStringToNull="true"
DbType="Int32"
DefaultValue=""
Name="id_convenzionato" />
<asp:ControlParameter
ControlID="_ddlVeicoli"
PropertyName="SelectedValue"
Direction="Input"
ConvertEmptyStringToNull="true"
DbType="Int32"
DefaultValue=""
name="id_veicolo" />
<asp:ControlParameter
ControlID="_ddlModalitaNoleggio"
PropertyName="SelectedValue"
Direction="Input"
ConvertEmptyStringToNull="true"
DbType="String"
DefaultValue=""
Name="modalita_noleggio" />
<asp:ControlParameter
ControlID="_cbVIP"
PropertyName="Checked"
DefaultValue="False"
Direction="Input"
DbType="String"
name="vip" />
<asp:ControlParameter
Direction="Input"
DbType="String"
ControlID="_txtSerie"
ConvertEmptyStringToNull="true"
PropertyName="Text"
DefaultValue=""
Name="serie" />
</SelectParameters>
I cannot use ControlParameters for every parameter because I need to pre-parse values in controls prior executing the query.
protected void _sdsNoleggi_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
string[] _tokens = _ddlMese.SelectedItem.Value.Split('/');
e.Command.Parameters["mese"].Value = _tokens[0];
e.Command.Parameters["anno"].Value = _tokens[1];
_tokens = _txtConvenzionato.Text.Split('\t');
if (_tokens.Length >= 3)
{
String _value = _tokens[_tokens.Length - 1].Trim(']', '[');
e.Command.Parameters["id_convenzionato"].Value = _value;
}
}
protected void _sdsVeicoli_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
string[] _tokens = _txtConvenzionato.Text.Split('\t');
if (_tokens.Length >= 3)
{
String _value = _tokens[_tokens.Length - 1].Trim(']', '[');
e.Command.Parameters["id_convenzionato"].Value = _value;
}
}
Both queries are triggered pressing on _btnRicerca button:
protected void _btnRicerca_Click(object sender, EventArgs e)
{
_sdsVeicoli.Select(DataSourceSelectArguments.Empty);
_sdsNoleggi.Select(DataSourceSelectArguments.Empty);
}
_ddlMese DropDownList is feed in codebehind:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) {
_ddlMese.Items.Clear();
getMonths();
}
}
private void getMonths() {
DateTime _date = DateTime.Now;
_ddlMese.Items.Add(
new ListItem(
_date.AddMonths(-3).ToString("MMMM/yyyy", CultureInfo.GetCultureInfoByIetfLanguageTag("it")),
_date.AddMonths(-3).ToString("MM/yyyy")));
_ddlMese.Items.Add(
new ListItem(
_date.AddMonths(-2).ToString("MMMM/yyyy", CultureInfo.GetCultureInfoByIetfLanguageTag("it")),
_date.AddMonths(-2).ToString("MM/yyyy")));
_ddlMese.Items.Add(
new ListItem(
_date.AddMonths(-1).ToString("MMMM/yyyy", CultureInfo.GetCultureInfoByIetfLanguageTag("it")),
_date.AddMonths(-1).ToString("MM/yyyy")));
ListItem _selectedItem = new ListItem(
_date.ToString("MMMM/yyyy", CultureInfo.GetCultureInfoByIetfLanguageTag("it")),
_date.ToString("MM/yyyy"));
_selectedItem.Selected = true;
_ddlMese.Items.Add(_selectedItem);
}
When I click on _btnRicerca I tought both queries should be executed and EVERY databound controls should be updated but this doesn't happen in my case.
_gvNoleggi is populated only at initial form load and then is never updated at least it never gets updated/filtered values from _sdsNoleggi. _ddlVeicoli is never populated (even if I set a correct value in _txtConvenzionato).
If I inspect e.Command.Parameters in _sdsNoleggi_Selecting or _sdsVeicoli_Selecting I clearly see that every parameter is set with the correct value (taken from controls) but the query seems not to use such values.
Where I'm failing?
There's a way to inspect the query that is actually performed withing a SQLDataSource? I mean the SelectCommand with actual parameters set?
How can I get how many records a SQLDataSource SelectCommand execution returns?
Best regards,
Mike
SQLDataSource SelectParameters value do not set
