SQL command

[jwrz200t]

i have a date field in the database which was declared as a varchar datatype. Because of that iam having problems sorting this date field inside the grid. Can anyone give me a SQL command to convert the varchar data type to a datetime data type??

 

[jbenson001]

Your previous 2 questions were answered here:
thread855-1281244

 

[jwrz200t]

ur command works perfectly but i want to change the DATE_REPORTED column to a datetime, but in ur command we are creating an alias column mydate. how can i get it right???

 

[jbenson001]

Change the AS to AS DATE_REPORTED

 

[jwrz200t]

Its still not working. I have a form with the date and time fields which are drop down boxes and the code is as follows:

private void ddlTimeDataBinding()
{
string[] strHourList = { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" };
ddlHrentered.DataSource = strHourList;
ddlHrentered.DataBind();
ddlHrShift.DataSource = strHourList;
ddlHrShift.DataBind();
string[] strMinuteList = { "00", "05", "10", "15", "20", "25", "30", "35", "40", "45", "50", "55" };

ddlMinentred.DataSource = strMinuteList;
ddlMinentred.DataBind();
ddlMinShift.DataSource = strMinuteList;
ddlMinShift.DataBind();
}
private void ddlDateDataBinding()
{
string[] strMonthList = { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12" };
string[] strDayList = { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31" };

string strYear1 = DateTime.Now.Year.ToString();//Current year
int intYear = int.Parse(strYear1) - 1;
string strYear2 = intYear.ToString();//Last year
string[] strYearList = { strYear1, strYear2 };
ddlMonth.DataSource = strMonthList;
ddlMonth.DataBind();
ddlDay.DataSource = strDayList;
ddlDay.DataBind();
ddlYear.DataSource = strYearList;
ddlYear.DataBind();
ddlMonthReported.DataSource = strMonthList;
ddlMonthReported.DataBind();
ddlDayReported.DataSource = strDayList;
ddlDayReported.DataBind();
ddlYearReported.DataSource = strYearList;
ddlYearReported.DataBind(); }

I hope this gives u a better idea of my problem. I would be waiting for ur reply.

 

[jbenson001]

This is just showing me the binding of the ddls... what exactly is the problem?

 

[jwrz200t]

thanx benson i got the sorting to work properly now, i just changed the datatype from varchar to datetime. now can i any means just dispaly only the date??? and also when i sort a perticular field and move on to the next page the sorting is lost how can i overcome these problems
???

 

[jbenson001]

YOu can use the itemdatabound of the datagrid or rowdatabound of the gridview to change the datetime columnt to show just the date

 

[jwrz200t]

can you please be more clear because iam really new to all these stuff.This is what i have

<asp:BoundField DataField="DATE_ENTERED" HeaderText="Date Entered" SortExpression = "DATE_ENTERED" />

what should i do now??

 

[jbenson001]

is this a datagrid or a gridview control?

 

[jwrz200t]

its a gridview control. Now most of the things are working fine but there is still a small problem. When i click on the headers to sort the column of the grid the dataset is getting refreshed. And when i move on to the next page the sort order is not maintained. So how do i stop the dataset from reloading when i moveon to the next page??

 

[jbenson001]

Where is the code that loads the data to the datset? It should be in the Page_Load with If not is postback around it

If NOT IsPostBack Then
''Load your dataset(s) here
End if

 

[jbenson001]

Since you are using C#:

if (!(IsPostBack)) { 
'Load your DataSet(s) here
}

 

[jwrz200t]

here is my code which loads the grid

private DataSet GetData()
{
string strUlid = Session["id"].ToString();
string strDdlValue = "";


if (ddlSeach.SelectedValue == "ID")
{
strDdlValue = "ID";
}
else if (ddlSeach.SelectedValue == "Date Entered")
{
strDdlValue = "DATE_ENTERED";
}
else
{
strDdlValue = "LAST_NAME";
}

OleDbConnection myConnection = new OleDbConnection(System.Configuration.ConfigurationManager.AppSettings["conMYSITE"]);

myConnection.Open();
OleDbDataAdapter ad = new OleDbDataAdapter("Select * FROM LOG WHERE " + strDdlValue + " LIKE '%" + tbSVSearch.Text + "%' ORDER BY DATE_ENTERED DESC", myConnection);

DataSet ds = new DataSet();

ad.Fill(ds);

return ds;
}

I hope iam clear

 

[jbenson001]

where do you call your GetData() function?

 

[jwrz200t]

private void SortGridView(string sortExpression, string direction)
{
DataTable dt = GetData().Tables[0];

DataView dv = new DataView(dt);

dv.Sort = sortExpression + " " + direction;

GridView1.DataSource = dv;

GridView1.DataBind();
}

 

[jbenson001]

in your sortgridview function try:

if (!(IsPostBack)) { 
DataTable dt = GetData().Tables[0];

        DataView dv = new DataView(dt);

}