Solved

SQL String manipulation

Posted on 2013-01-29
19
225 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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
 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

751 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