I have a table in Sql Database And in table there in more then 40,000 record...,
Now on one page of my site,,
i want to show that data according to
Dynamic query(add Multiple Query According to condition),and then return all result of this query to DataTable and Then Put It into GRIDVIew,
Now My problem is, when i want to show all result at a time then it gives error Like this..
Timeout expired. The timeout period elapsed prior to complete
or
Query Return Nothing,,,
And when i take data according to some condition then It Return perfect data(Means When Result of data table is low then it work fine...),
This is My Code:
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("SELECT t.id, t.year,t.courtname,t.partyname,t.itano,t.favour,t.sectionno, STUFF((SELECT ' ' + s.sitentionno ,' ' + s.subjudgements,' ' + s.page , ' ' FROM tblJudgements s WHERE s.partyname = t.partyname FOR XML PATH('')),1,0,'') AS Citation FROM tblJudgements AS t where t.categoryname='Direct Taxes' ");
if (txtWordAd.Text != "")
{
string keywords = txtWordAd.Text;
if (!string.IsNullOrEmpty(keywords))
{
string[] words = keywords.Split(' ');
for (int i = 0; i < words.Length; i++)
{
if (i == 0)
{
strSql2.Append(" and ( t.judgement like '%[^a-z]" + words[i].ToString().Trim() + "[^a-z]%' ) ");
}
else
{
strSql2.Append(" and judgement like '%[^a-z]" + words[i].ToString().Trim() + "[^a-z]%'");
}
}
}
}
if (txtDateFromAd.Text != "" && txtDateToAd.Text != "")
{
strSql2.Append(" and (dateofjudgement between '" + txtDateFromAd.Text + "' and '" + txtDateToAd.Text + "' ) ");
}
SqlDataAdapter adp4 = new SqlDataAdapter(strSql2.ToString(), strConn);
adp4.Fill(dt);
GridJudgementAdvanced1.DataSource = dt;
GridJudgementAdvanced1.DataBind();
}
i also try :adp4.SelectCommand.CommandTimeout = 120;
but it not work,,
i apply pagging to GridView 20 record at a time, But Problem is Query Return All 40000 record to datatable And this Consume So much Time, to complete one Query(Process...),
So in that Situation What can i Do ???????
Plz help..
I Also Find Same Problem Here,But There is no solution..
[
^]
How to resolve Time out Error in Classic ASP[
^]
Timeout expired sql server problem[
^]