SQL String manipulation

I have to do this fairly quick so while Im trying to do it I figured Id post it as well...I need to change the order by clause depending on which column I click to change the "Order By" statement for the applicable column. Im trying to do a Replace, which was my first thought, in the Order By statement. For example

Select * from tableA Order By PermitID asc

When I click on a column I want to change the Order By clause to read

Select * from tableA Order By StartDate desc

All I need to know is how I can replace the Order By with the new Order By in the string in ASP.net code?

Thanks everyone
jknj72Asked:
Who is Participating?
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
 ThisSQL = "SELECT * FROM VW_TPSA_SEARCH_RESULTS WHERE PERMIT_ID > 0 AND UPPER(DEPT_NAME)='TECHNOLOGY' ORDER BY PERMIT_ID"

Dim tokens() As String = ThisSQL.Split(New String() {"ORDER BY"}, StringSplitOptions.RemoveEmptyEntries)
Dim newthisSql As String = (tokens(0) + " ORDER BY Employee_Name desc")

Open in new window

0
 
KhiluCommented:
Pass your desired column name in columnName

 string sqlQuery = string.Format("Select * from tableA Order By {0} asc", columnName);

Open in new window

0
 
Easwaran ParamasivamCommented:
Simple. Use String.Format() to form your query.

Have your query as given below:

string myquery = String.Format("Select * from tableA Order By {0} {1}",  YOURCOLUMN, ASCorDESC );

Refer: http://csharp.net-informations.com/string/csharp-string-format.htm and http://www.dotnetperls.com/string-format for more information.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Meir RivkinFull stack Software EngineerCommented:
have a template for the order by query:

enum OrderDirection{Asc,Desc};

string orderByQuery = "Select * from tableA Order By {0} {1}";
string lastOrderField = null;
OrderDirection lastOrderDir = OrderDirection;

whenever u wish to do select ordered by field, use string.format() to modify your select query.

the lastOrderField is used to determine the direction order, if you order by field asc, then u order the same thing again, it will change the order direction to desc.

so assume u want to order by ID:
//default order is desc
lastOrderDir = ("ID" == lastOrderField ? (lastOrderDir == OrderDirection.Asc ? OrderDirection.Desc : OrderDirection.Asc ) : OrderDirection.Desc);

//update current order field/direction
lastOrderField = "ID";
 
var sql = string.Format(orderByQuery, "ID", lastOrderField.ToString());

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
here's a function you can use to create the query:
private string CreateOrderQuery(string field){

//update current order field
lastOrderField =field;

lastOrderDir = (field == lastOrderField ? (lastOrderDir == OrderDirection.Asc ? OrderDirection.Desc : OrderDirection.Asc ) : OrderDirection.Desc);
 
return string.Format(orderByQuery, field, lastOrderField.ToString());
}

Open in new window

0
 
jknj72Author Commented:
My query sql string is in a property ThisSql so I created a function called ManageOrderBy and pass in ThisSql and from there I want to change the order by clause. I have the column name and the direction to sort which I am also passing in. I notice that you all are using brackets around the field needed to be replaced? I am using VB.NET(I should said that in the first place) so Im a little confused

Private Function ManageOrderBy(sSql As String, sExpression As String, sDirection As String) As String

Can you dumb it down just a little further for me please....THANKS
0
 
Meir RivkinFull stack Software EngineerCommented:
what sExpression  means?
you should pass only the field and direction, the query format can be const string member of the class.
Const sOrderBySelectFormat As String = "Select * from tableA Order By {0} {1}"

Open in new window


and the function:
 Private Function ManageOrderBy(sField As String, sDirection As String) As String

return string.Format(sOrderBySelectFormat, sField , sDirection)

End function

Open in new window

0
 
jknj72Author Commented:
sExpression is the field name and sDirection is ascending or descending
0
 
Meir RivkinFull stack Software EngineerCommented:
ok so sField and sExpression are the same, take it from here
0
 
jknj72Author Commented:
thats correct but I checked the string after the function call and it is the same as it was prior to the function call

ThisSQL = "SELECT * FROM VW_TPSA_SEARCH_RESULTS WHERE PERMIT_ID > 0 AND UPPER(DEPT_NAME)='TECHNOLOGY' ORDER BY PERMIT_ID"
sortExpression = "Employee_Name"
sortDirection = "Descending"

Function call
ThisSQL = ManageOrderBy(ThisSQL , sortExpression, sortDirection)

Function
Private Function ManageOrderBy(sExpression As String, sDirection As String) As String

        Return String.Format(ThisSQL, sExpression, sDirection)

 End Function

After the call ThisSQL remains the same? What am I doing wrong here???
0
 
jknj72Author Commented:
The signature should be

Private Function ManageOrderBy(sSQL as string, sExpression As String, sDirection As String) As String

Sorry

FYI, I tried both ways, with Constant and passing it into function
0
 
Meir RivkinFull stack Software EngineerCommented:
cause the sSql is not formatted properly.

the ThisSQL contains "ORDER BY PERMIT_ID", but you pass different field to your function:
sortExpression = "Employee_Name"


do you want to order by multiple fields?
0
 
jknj72Author Commented:
Can I still do this without the brackets around the fields I want to replace?
0
 
jknj72Author Commented:
No I just want Order by Permit_ID asc to be Order By Employee_Name desc
0
 
Meir RivkinFull stack Software EngineerCommented:
so change ThisSQL to ordeby Employee_Name.
why do u need the function for?

if you need a general function which creates an order by query than u need a function but otherwise simply change the ThisSQL to:
ThisSQL = "SELECT * FROM VW_TPSA_SEARCH_RESULTS WHERE PERMIT_ID > 0 AND UPPER(DEPT_NAME)='TECHNOLOGY' ORDER BY Employee_Name desc"
0
 
jknj72Author Commented:
ThisSQL is already set and I just need to change the Order By, can this be done? I cant change the SQL it is already set.
0
 
jknj72Author Commented:
Thats what I was looking for....Thanks sedgwick
0
 
jknj72Author Commented:
Please go to my next question that has to do with this one for sorting date fields!!!
0
 
Meir RivkinFull stack Software EngineerCommented:
Post the url here
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.