To populate gridview based on multiple serach criterias including date

[vijeevvv]

Hi,
In my proj i need to populate a gridview based on the selected value. I have abt 5 to 6 fields for searching including dropdown list and textboxes. Dropdown list is for country, region etc.. and the textboxes are for from_date and to_date. i'm using ms-access DB. so when i write the coding, it works fine if coding for all the other fields are written(except dates) or else only for dates alone.. but wen i combine these two the prob starts.. Also at the same time, if user does not enter the date but selects the remaining fields, it shows no records even if ther are some...
Now this s wat i want exactly::
Initially all the records will be displayed...
Then based on dropdown selection the reults in the grid should change...
Some things to note:
1.DDL may be with default value "Any" and dates may/may not be specified
2.DDL can be selected with any value and dates may/may not be specified

I ll post a part of the coding... so it helps better...

<asp:AccessDataSource ID="AccessDataSource1" runat="server" 

DataFile="~/smsdb.accdb" 



SelectCommand="SELECT * FROM [smsMessage]" FilterExpression="Partner like '{0}%' AND Country like '{1}%' AND Region like '{2}%' AND MessageDirection like '{3}%'"> 



<FilterParameters> 



<asp:ControlParameter ControlID="ddlPartner" Name="Partner" PropertyName="SelectedValue" 

Type="String" /> 



<asp:ControlParameter ControlID="ddlCountry" Name="Country" PropertyName="SelectedValue" 

Type="String" /> 



<asp:ControlParameter ControlID="ddlRegion" Name="Region" 

PropertyName="SelectedValue" Type="String" /> 



<asp:ControlParameter ControlID="ddlMsgDirection" Name="MessageDirection" PropertyName="SelectedValue" 

Type="String" /> 



</FilterParameters> 



</asp:AccessDataSource> 



<asp:AccessDataSource ID="AccessDataSource2" runat="server" 

DataFile="~/smsdb.accdb" 



SelectCommand="SELECT DISTINCT [Country] FROM [smsMessage]"> 



</asp:AccessDataSource> 



<asp:AccessDataSource ID="AccessDataSource3" runat="server" 

DataFile="~/smsdb.accdb" 



SelectCommand="SELECT DISTINCT [Region] FROM [smsMessage]"> 



</asp:AccessDataSource> 



<asp:AccessDataSource ID="AccessDataSource4" runat="server" 

DataFile="~/smsdb.accdb" 



SelectCommand="SELECT DISTINCT [Partner] FROM [smsMessage]"> 



</asp:AccessDataSource> 



<asp:AccessDataSource ID="AccessDataSource5" runat="server" 

DataFile="~/smsdb.accdb" 



SelectCommand="SELECT DISTINCT [MessageDirection] FROM [smsMessage]"> 



</asp:AccessDataSource>

So here each of the datasource selects the distinct fields.. and this works correct without dates,..
Someone pls help me with using dates... Or a new approach could be helpful...
Thanks...:-)

[prvnkmr194]

Its not the way to post your code, use code tag to post code
Code for select statement

SELECT Table1.number1, Table1.datetime1
FROM Table1
WHERE (((Table1.number1)=1) AND ((Table1.datetime1)>#1/1/2001# )And (Table1.datetime1)<#1/1/2002#);

Its an example to query about date and other filed both are working at same time.
You the if condition if any filed is blank to generate the different select that is either you provide default value or what you want to do its all up to you.
Hope it will help you,otherwise feel free to ask any query with your code in code tag

[vijeevvv]

Thanks for the reply.. Now,

SelectCommand="SELECT * FROM [PPP_SMS]" 
FilterExpression="((partner_name like '{0}%') AND (country like '{1}%') AND (region like '{2}%') AND (message_direction like '{3}%') AND ((sms_date)>#{4}# And (sms_date)<#{5}#))">
        <FilterParameters>
         <asp:ControlParameter ControlID="ddlPartner" Name="partner_name" PropertyName="SelectedValue" Type="String" />
            <asp:ControlParameter ControlID="ddlCountry" Name="Country" PropertyName="SelectedValue" Type="String" />
            <asp:ControlParameter ControlID="ddlRegion" Name="Region" PropertyName="SelectedValue" Type="String" />
            <asp:ControlParameter ControlID="ddlMsgDirection" Name="MessageDirection" PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="txtFromDate" Name="FromDate" PropertyName="Text" Type="DateTime"/>
                 <asp:ControlParameter ControlID="txtToDate" Name="ToDate" PropertyName="Text" Type="DateTime"/>
        </FilterParameters>

This is my select command.. the prob is,, only when i give the date its working.. if i don't enter the date none of the select criteria is working..

[prvnkmr194]

I like to give you hint how to write code.......
use two query string and check if date is empty then use other query

string strDate="SELECT Table1.number1, Table1.datetime1
FROM Table1
WHERE (((Table1.number1)=1) AND ((Table1.datetime1)>#1/1/2001# )And (Table1.datetime1)<#1/1/2002#)";

string str1="SELECT Table1.number1, Table1.datetime1
FROM Table1
WHERE (((Table1.number1)=1) ";

if (date1)
{
//use the strDate
}
else
{
//str1
}

I hope You understand, its just a hint ok

[vijeevvv]

yes wat u say is correct only.. i tried tat method already.. the prob wit tat was i hav many search fields in some cases 8 also.. so i need to populate the gridview based on tat.. i hav to write so many 'if' loops which ll not sound good.. so i decided to configure the datasource to grid without writin code in .cs file... can u pls help me out in this context... The code i posted previously is my select command..

[vijeevvv]

ok i tried to write the code in the .cs file itself.. and its working fine.. thank u.. but again the prob is with the date.. I get an error statin that "string was not recognized as a valid datetime".. I dono wat s de exact prob.. can u plz help me out.. the txtFromDate field has the date that is selected from a calendar control..

if(txtFromDate.Text=="")
        {
            AccessDataSource1.SelectCommand="SELECT * FROM [PPP_SMS]";
            AccessDataSource1.FilterExpression="(partner_name like '{0}%') AND (country like '{1}%') AND (region like '{2}%') AND (message_direction like '{3}%')";
            AccessDataSource1.FilterParameters.Add(new ControlParameter("txt","ddlCountry","SelectedValue"));
            AccessDataSource1.FilterParameters.Add(new ControlParameter("txt1","ddlPartner", "SelectedValue"));
            AccessDataSource1.FilterParameters.Add(new ControlParameter("txt2","ddlRegion","SelectedValue"));
            AccessDataSource1.FilterParameters.Add(new ControlParameter("txt3","ddlMsgDirection","SelectedValue"));
            gvSms.DataSource = AccessDataSource1;
            gvSms.DataBind();
        }


        else if (txtFromDate.Text != "")
        { 
            AccessDataSource1.SelectCommand = "SELECT * FROM [PPP_SMS]";
            AccessDataSource1.FilterExpression = "(partner_name like '{0}%') AND (country like '{1}%') AND (region like '{2}%') AND (message_direction like '{3}%') AND ((sms_date) = #{4}#)";
            AccessDataSource1.FilterParameters.Add(new ControlParameter("txt", "ddlCountry", "SelectedValue"));
            AccessDataSource1.FilterParameters.Add(new ControlParameter("txt1", "ddlPartner", "SelectedValue"));
            AccessDataSource1.FilterParameters.Add(new ControlParameter("txt2", "ddlRegion", "SelectedValue"));
            AccessDataSource1.FilterParameters.Add(new ControlParameter("txt3", "ddlMsgDirection", "SelectedValue"));
            AccessDataSource1.FilterParameters.Add(new ControlParameter("txt4", "txtFromDate", "Text"));
            gvSms.DataSource = AccessDataSource1;
            gvSms.DataBind();
        }

[prvnkmr194]

You have to enclose your date within # symbol that is
some thing like this suppose

string str ="#"+txtFromDate.text+"#"

txtFromDate is your textbox which collect value from calander control may in the formate mm/dd/yyyy
example of query with date field

string date1="#"+txtFromDate+"#"; //date1="#5/15/2010#" now date1 contain 
str="select * from table1 where date_field='"+date1+"'";

I hope will help you other wise feel free to ask your problem with your error

[vijeevvv]

Ok somehow i got rid of that prob and gettin the search results properly... Thanks a lot..

In my proj i'm using ms-access DB.. i have two fields namely sms_date,sms_time.. i myself enter the records to the DB..

I need to populate the gridview with the records within the given range of date.. i hav a calendar control to select the date(mm/dd/yyyy) and user should type the time(hh:mm:ss).
But the thing is tat wen i select the date from DB, i get the time also in gridview date field .. ex: 1/3/2011 12:00:00 AM.

Similarly for the time field, it comes default wit the date.. ex: 12/30/1899 12:45:23 AM..
So its givin me error wen i check these date and the date wat i enter in the front end..

In the access DB itself i set the format for sms_date as Shortdate and for time as ShortTime.. but wen i get the values into the gridview it comes as date and time by default..

[vijeevvv]

i found out answer for the date and time issue:
In the gridview's field tat require date and time jus specify the DataFormatString:

<asp:BoundField DataField="payment_date" HeaderText="Date" DataFormatString="{0:d}"/>
<asp:BoundField DataField="sms_time" SortExpression="SmsTime" DataFormatString="{0:T}"/>

thats it.. it works fine:-)
d-short date(mm/dd/yyyy)
T-Long time(hh:mm:ss)
t-short time(hh:mm)