Solved

SQL String manipulation

Posted on 2013-01-29
19
219 Views
Last Modified: 2013-01-29
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
0
Comment
Question by:jknj72
19 Comments
 
LVL 3

Expert Comment

by:Khilu
ID: 38830941
Pass your desired column name in columnName

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

Open in new window

0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 38830948
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 38830959
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 38830967
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
 

Author Comment

by:jknj72
ID: 38831022
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 38831062
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
 

Author Comment

by:jknj72
ID: 38831067
sExpression is the field name and sDirection is ascending or descending
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 38831074
ok so sField and sExpression are the same, take it from here
0
 

Author Comment

by:jknj72
ID: 38831135
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:jknj72
ID: 38831139
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 38831269
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
 

Author Comment

by:jknj72
ID: 38831276
Can I still do this without the brackets around the fields I want to replace?
0
 

Author Comment

by:jknj72
ID: 38831300
No I just want Order by Permit_ID asc to be Order By Employee_Name desc
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 38831321
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
 

Author Comment

by:jknj72
ID: 38831353
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
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 38831403
 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
 

Author Closing Comment

by:jknj72
ID: 38831897
Thats what I was looking for....Thanks sedgwick
0
 

Author Comment

by:jknj72
ID: 38831903
Please go to my next question that has to do with this one for sorting date fields!!!
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 38831910
Post the url here
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now