Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL String manipulation

Posted on 2013-01-29
19
Medium Priority
?
239 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:Meir Rivkin
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 42

Expert Comment

by:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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:
Meir Rivkin earned 2000 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:Meir Rivkin
ID: 38831910
Post the url here
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Integration Management Part 2
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

824 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