[RESOLVED] Default sort not working in gridview

Hi,

On the webpage I have designed, I have set up sort links to all the columns but it only works when the links are clicked.

I intend to set a default sort when the results are displayed in the gridview the first time. To accomplish this, I added the SQL query 'order by column asc' in the BindGridView. The default sort now works but when I click on the sort links again, I get
an error -  Incorrect syntax near the keyword 'order'.

Could somebody please tell what modifications / additions I need to make in the following code so that both the default sort and sorting links work. The relevant codes are attached below:

private void GetSortingDetails(ref string sortexpression, ref string sortOrder)
        {
            if (sortexpression == null && sortOrder == null)
            {
                if (!string.IsNullOrEmpty(hdCurrentSortDirection.Value))
                {
                    if (hdCurrentSortDirection.Value.ToLower() == "descending")
                        sortOrder = "desc";
                }
                if (!string.IsNullOrEmpty(hdCurrentSortColumn.Value))
                {
                    sortexpression = hdCurrentSortColumn.Value;
                }
            }
        }

private void BindGridView(string sortexpression = null, string sortOrder = null)    // binds the data 
        {
            
                    
            DataTable dt = new DataTable();
            SqlConnection connection = new SqlConnection(GetConnectionString());

            GetSortingDetails(ref sortexpression, ref sortOrder);
            try
            {
                connection.Open();
                StringBuilder sbQuery = new StringBuilder();
                sbQuery.Append(" SELECT Row_Id,");
                sbQuery.Append(" (Select top 1 function_name from [FUNCTION] where FUNCTION_ID=HD.FUNCTION_ID) as function_name ,");
                sbQuery.Append(" (Select top 1 Sub_function_name from [SUB_FUNCTION] where SUB_FUNCTION_ID=HD.SUB_FUNCTION_ID) as [SUB_FUNCTION],");
                sbQuery.Append(" (Select top 1 system_name from [SYSTEM] where SYSTEM_ID=HD.SYSTEM_ID) as  system_name, ");
                sbQuery.Append(" [DIRECTION],");
                sbQuery.Append(" (Select top 1 system_name from [SYSTEM] where SYSTEM_ID=HD.REMOTE_SYSTEM_id) as  [REMOTE_SYSTEM], ");
                sbQuery.Append(" (Select top 1 INTERFACE_METHOD from [INTERFACE_METHOD] where INTERFACE_ID=HD.INTERFACE_ID) as [INTERFACE_METHOD],");
                sbQuery.Append(" Remarks,[LAST_UPDATE_DT],[LAST_UPDATE_USER],[CREATE_DT],[CREATE_USER]  FROM header HD");
                sbQuery.Append(" WHERE office_cd = @Value1 AND trade_cd = @Value2 and (DELETED_FLG <> 'Y' or  DELETED_FLG is NULL) order by function_name asc");
                
                if (!string.IsNullOrEmpty(sortexpression))
                    sbQuery.Append(" order by " + sortexpression);
                if (!string.IsNullOrEmpty(sortOrder))
                    sbQuery.Append(" " + sortOrder);


                SqlCommand sqlCmd =
                    new SqlCommand(
                        sbQuery.ToString(),
                        connection);

                string OfficeCd = "";
                if (Office_cd.SelectedItem != null)
                {
                    OfficeCd = Office_cd.SelectedItem.Text;
                }
                string TradeCd = "";
                if (trade_cd.SelectedItem.Text != null)
                {
                    TradeCd = trade_cd.SelectedItem.Value;
                }
                sqlCmd.Parameters.AddWithValue("@Value1", OfficeCd);
                sqlCmd.Parameters.AddWithValue("@Value2", TradeCd);

                SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

                sqlDa.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
                else
                {
                    ShowNoResultFound(dt, GridView1);
                }
                GridView1.FooterRow.Visible = false;
                GridView1.Columns[GridView1.Columns.Count - 1].Visible = false;
                hdnNewRecordRequsted.Value = "0";
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string msg = "Fetch Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
            finally
            {
                connection.Close();
            }
        }

 

 

Check your logic.  It appears that you may add 2 Order By clauses with your code:

sbQuery.Append(" WHERE office_cd = @Value1 AND trade_cd = @Value2 and (DELETED_FLG <> 'Y' or  DELETED_FLG is NULL) order by function_name asc");
                
                if (!string.IsNullOrEmpty(sortexpression))
                    sbQuery.Append(" order by " + sortexpression);
                if (!string.IsNullOrEmpty(sortOrder))
                    sbQuery.Append(" " + sortOrder);

If sortexpression is not empty, it will append sbQuery.Append(" order by " + sortexpression);  when you already have  " WHERE office_cd = @Value1 AND trade_cd = @Value2 and (DELETED_FLG <> 'Y' or DELETED_FLG is NULL)
order by function_name asc");

paindaasp

Check your logic.  It appears that you may add 2 Order By clauses with your code:

sbQuery.Append(" WHERE office_cd = @Value1 AND trade_cd = @Value2 and (DELETED_FLG <> 'Y' or  DELETED_FLG is NULL) order by function_name asc");
                
                if (!string.IsNullOrEmpty(sortexpression))
                    sbQuery.Append(" order by " + sortexpression);
                if (!string.IsNullOrEmpty(sortOrder))
                    sbQuery.Append(" " + sortOrder);

If sortexpression is not empty, it will append sbQuery.Append(" order by " + sortexpression);  when you already have  " WHERE office_cd = @Value1 AND trade_cd = @Value2 and (DELETED_FLG <> 'Y' or DELETED_FLG is NULL)
order by function_name asc");

Thanks for your reply. However, I wasnt quite able to understand what you meant. Could you please advice what changes should I make to achieve the default sort order ?

Thanks.

 

Try changing your Select creation statements, and the 2 following if statements to this:

sbQuery.Append(" SELECT Row_Id,");
sbQuery.Append(" (Select top 1 function_name from [FUNCTION] where FUNCTION_ID=HD.FUNCTION_ID) as function_name ,");
sbQuery.Append(" (Select top 1 Sub_function_name from [SUB_FUNCTION] where SUB_FUNCTION_ID=HD.SUB_FUNCTION_ID) as [SUB_FUNCTION],");
sbQuery.Append(" (Select top 1 system_name from [SYSTEM] where SYSTEM_ID=HD.SYSTEM_ID) as  system_name, ");
sbQuery.Append(" [DIRECTION],");
sbQuery.Append(" (Select top 1 system_name from [SYSTEM] where SYSTEM_ID=HD.REMOTE_SYSTEM_id) as  [REMOTE_SYSTEM], ");
sbQuery.Append(" (Select top 1 INTERFACE_METHOD from [INTERFACE_METHOD] where INTERFACE_ID=HD.INTERFACE_ID) as [INTERFACE_METHOD],");
sbQuery.Append(" Remarks,[LAST_UPDATE_DT],[LAST_UPDATE_USER],[CREATE_DT],[CREATE_USER]  FROM header HD");
sbQuery.Append(" WHERE office_cd = @Value1 AND trade_cd = @Value2 and (DELETED_FLG <> 'Y' or  DELETED_FLG is NULL)");
                
if (!string.IsNullOrEmpty(sortexpression))
{
  sbQuery.Append(" order by " + sortexpression);
}
else
{
  sbQuery.Append(" order by function_name ");
}

if (!string.IsNullOrEmpty(sortOrder))
{
  sbQuery.Append(" " + sortOrder);
}
else
{
  sbQuery.Append(" asc");
}

paindaasp

Try changing your Select creation statements, and the 2 following if statements to this:

sbQuery.Append(" SELECT Row_Id,");
sbQuery.Append(" (Select top 1 function_name from [FUNCTION] where FUNCTION_ID=HD.FUNCTION_ID) as function_name ,");
sbQuery.Append(" (Select top 1 Sub_function_name from [SUB_FUNCTION] where SUB_FUNCTION_ID=HD.SUB_FUNCTION_ID) as [SUB_FUNCTION],");
sbQuery.Append(" (Select top 1 system_name from [SYSTEM] where SYSTEM_ID=HD.SYSTEM_ID) as  system_name, ");
sbQuery.Append(" [DIRECTION],");
sbQuery.Append(" (Select top 1 system_name from [SYSTEM] where SYSTEM_ID=HD.REMOTE_SYSTEM_id) as  [REMOTE_SYSTEM], ");
sbQuery.Append(" (Select top 1 INTERFACE_METHOD from [INTERFACE_METHOD] where INTERFACE_ID=HD.INTERFACE_ID) as [INTERFACE_METHOD],");
sbQuery.Append(" Remarks,[LAST_UPDATE_DT],[LAST_UPDATE_USER],[CREATE_DT],[CREATE_USER]  FROM header HD");
sbQuery.Append(" WHERE office_cd = @Value1 AND trade_cd = @Value2 and (DELETED_FLG <> 'Y' or  DELETED_FLG is NULL)");
                
if (!string.IsNullOrEmpty(sortexpression))
{
  sbQuery.Append(" order by " + sortexpression);
}
else
{
  sbQuery.Append(" order by function_name ");
}

if (!string.IsNullOrEmpty(sortOrder))
{
  sbQuery.Append(" " + sortOrder);
}
else
{
  sbQuery.Append(" asc");
}

Thanks very much for the solution you provided !! It worked perfectly.

Just one small question, I wish to use the keyword 'desc' along with the order by in the above example. I tried to do it like ''order by function_name desc" but it returns an error.

Do you know a way out to accomplish this ?]

Thanks again.

 

 

If you are adding the desc to:

                
if (!string.IsNullOrEmpty(sortexpression))
{
  sbQuery.Append(" order by " + sortexpression);
}
else
{
  sbQuery.Append(" order by function_name desc ");
}

Then you are probably creating your initial problem, but with your sort order, instead of the ORDER BY clause.  The select will contain order by function_name desc and when you hit:

if (!string.IsNullOrEmpty(sortOrder))
{
  sbQuery.Append(" " + sortOrder);
}
else
{
  sbQuery.Append(" asc");
}

it will probably add " asc", causing the error.  If you want the initial sort to be descending change " asc" to " desc".

 

paindaasp

If you are adding the desc to:

                
if (!string.IsNullOrEmpty(sortexpression))
{
  sbQuery.Append(" order by " + sortexpression);
}
else
{
  sbQuery.Append(" order by function_name desc ");
}

Then you are probably creating your initial problem, but with your sort order, instead of the ORDER BY clause.  The select will contain order by function_name desc and when you hit:

if (!string.IsNullOrEmpty(sortOrder))
{
  sbQuery.Append(" " + sortOrder);
}
else
{
  sbQuery.Append(" asc");
}

it will probably add " asc", causing the error.  If you want the initial sort to be descending change " asc" to " desc".

 

 

Cheers ! thanks again, it worked perfectly.