Sql query for order by problem

Just as I expected. Dates stored in nvarchar column will be sorted as string so, "21/10/2016" will be greater than "01/12/2016" while in reality it is less. This is because 0 is less than 2. 

 

You have two options, either convert that nvarchar to date at runtime or change the column datatype (carefully! after making some safe copies).

 

The second option is the best.

 

Sorry - Too much code to read and comprehend. All I can say is you are too much coding too small a thing to achieve. Think of taking stored procedure approach to move all db related processing there.

string pqr1 = "select * from General_Cash_Flow where General_Item='" + cmbxCltId.Text + "' and CrtDate='" + login.setvalueyear + "' order by Dates Asc";

cmd = new SqlCommand(pqr1, cnn);

dr = cmd.ExecuteReader();

while (dr.Read())

{

string Id = dr["Id"].ToString();

id11 = Convert.ToInt32(Id);

string Dates = dr["Dates"].ToString();

dat2 = Dates;

string Credit = dr["Credit"].ToString();

crt = Credit;

string Debit = dr["Debit"].ToString();

drt = Debit;

}

 

i have some dates like -

 01/12/2016

20/11/2016

02/12/2016

28/11/2016

29/11/2016

 

now according to me date asc order start from 20/11/2016

but right now start from 01/12/2016

so i want to ask why .....

how to start from asc  

if ((fday < lday) && (fmonth == lmonth) && (fyear == lyear))

{

int id1 = 0;

cnn.Open();

string str9 = "Select Max(Id) from General_Cash_Flow";

cmd = new SqlCommand(str9, cnn);

da = new SqlDataAdapter(cmd);

dt = new DataTable();

da.Fill(dt);

cmd.ExecuteReader();

if (dt.Rows.Count > 0)

{

id1 = Convert.ToInt32(dt.Rows[0][0].ToString());

}

cnn.Close();

int cid = id1 + 1;

string amt = "";

string vtype = "Recipt";

cnn.Open();

string qry2 = "insert into General_Cash_Flow(Id,Dates,General_Item,Debit,Credit,Through,CheqNo,Total,VoucherType,CrtDate) values('" + cid + "','" + dddt + "','" + cmbxCltId.Text + "','" + amt + "','" + txtPaidAmount.Text + "','" + cmbxBankName.Text + "', '" + txtCheckNumb.Text + "','" + gentotal + "','" + vtype + "','" + login.setvalueyear + "')";

cmd = new SqlCommand(qry2, cnn);

cmd.ExecuteNonQuery();

cnn.Close();

Refresh();

string GItem = string.Empty;

string crt = string.Empty; string dat2 = string.Empty; string drt = string.Empty; float total; int id11;

cnn.Open();

string pqr1 = "select * from General_Cash_Flow where General_Item='" + cmbxCltId.Text + "' and CrtDate='" + login.setvalueyear + "' order by Dates Asc";

cmd = new SqlCommand(pqr1, cnn);

dr = cmd.ExecuteReader();

while (dr.Read())

{

string Id = dr["Id"].ToString();

id11 = Convert.ToInt32(Id);

string Dates = dr["Dates"].ToString();

dat2 = Dates;

string Credit = dr["Credit"].ToString();

crt = Credit;

string Debit = dr["Debit"].ToString();

drt = Debit;

if (count == 1)

{

float Gtotal = openamount;

cnn1.Open();

string sttr3 = "update General_Cash_Flow set Total='" + Gtotal + "' where General_Item='" + cmbxCltId.Text + "' and Dates='" + dat2 + "' and Id='" + id11 + "' and CrtDate='" + login.setvalueyear + "' ";

cmd1 = new SqlCommand(sttr3, cnn1);

cmd1.ExecuteNonQuery();

cnn1.Close();

cnn1.Open();

string sttr4 = "update Sale_Total set Ttlrcv='" + Gtotal + "' where General_Item='" + cmbxCltId.Text + "' and CrtDate='" + login.setvalueyear + "' ";

cmd1 = new SqlCommand(sttr4, cnn1);

cmd1.ExecuteNonQuery();

cnn1.Close();

Refresh();

count++;

}

else if (crt != "")

{

float salttl = 0;

cnn1.Open();

string st1 = "Select * from Sale_Total where General_Item ='" + cmbxCltId.Text + "' and CrtDate='" + login.setvalueyear + "'";

cmd1 = new SqlCommand(st1, cnn1);

da = new SqlDataAdapter(cmd1);

dt = new DataTable();

da.Fill(dt);

cmd1.ExecuteReader();

if (dt.Rows.Count > 0)

{

float Ttlrcv = float.Parse(dt.Rows[0][4].ToString());

salttl = Ttlrcv;

}

cnn1.Close();

float Gtotal3 = 0;

Gtotal3 = float.Parse(crt) + salttl;

cnn1.Open();

string sttr4 = "update Sale_Total set Ttlrcv='" + Gtotal3 + "' where General_Item='" + cmbxCltId.Text + "' and CrtDate='" + login.setvalueyear + "' ";

cmd1 = new SqlCommand(sttr4, cnn1);

cmd1.ExecuteNonQuery();

cnn1.Close();

cnn1.Open();

string sttr = "update General_Cash_Flow set Total='" + Gtotal3 + "' where General_Item='" + cmbxCltId.Text + "' and Dates='" + dat2 + "' and Id='" + id11 + "' and CrtDate='" + login.setvalueyear + "' ";

cmd1 = new SqlCommand(sttr, cnn1);

cmd1.ExecuteNonQuery();

cnn1.Close();

Refresh();

}

else if (drt != "")

{

float salttl = 0;

cnn1.Open();

string st1 = "Select * from Sale_Total where General_Item ='" + cmbxCltId.Text + "' and CrtDate='" + login.setvalueyear + "'";

cmd1 = new SqlCommand(st1, cnn1);

da = new SqlDataAdapter(cmd1);

dt = new DataTable();

da.Fill(dt);

cmd1.ExecuteReader();

if (dt.Rows.Count > 0)

{

float Ttlrcv = float.Parse(dt.Rows[0][4].ToString());

salttl = Ttlrcv;

}

cnn1.Close();

float Gtotal3 = 0;

Gtotal3 = float.Parse(drt) - salttl;

cnn1.Open();

string sttr4 = "update Sale_Total set Ttlrcv='" + Gtotal3 + "' where General_Item='" + cmbxCltId.Text + "' and CrtDate='" + login.setvalueyear + "' ";

cmd1 = new SqlCommand(sttr4, cnn1);

cmd1.ExecuteNonQuery();

cnn1.Close();

cnn1.Open();

string sttr = "update General_Cash_Flow set Total='" + Gtotal3 + "' where General_Item='" + cmbxCltId.Text + "' and Dates='" + dat2 + "' and Id='" + id11 + "' and CrtDate='" + login.setvalueyear + "' ";

cmd1 = new SqlCommand(sttr, cnn1);

cmd1.ExecuteNonQuery();

cnn1.Close();

Refresh();

}

Refresh();

}

dr.Close();

cnn.Close();

 

here date is also nvarchar

here i want to select data according to dates asc order one by one  please check

but this condition only apply for one month date but when date goes to last month then not working please help me.. 

The UPDATE and ORDER BY clauses are completed unrelated. 

 

You only mention whatever records you want to update in the WHERE clause of the update query. The database engine will take care of the rest.

 

ORDER BY clause is applicable only to the SELECT clause because the order in which server will return the rows is unpredictable (especially when primary key is missing).

 

I also suspect that in database you are having date column as a varchar and not date. Please clarify.