Solved

SQL String manipulation

Posted on 2013-01-29
19
217 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
sExpression is the field name and sDirection is ascending or descending
0
 
LVL 42

Expert Comment

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

Author Comment

by:jknj72
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:jknj72
Comment Utility
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
Comment Utility
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
Comment Utility
Can I still do this without the brackets around the fields I want to replace?
0
 

Author Comment

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

Expert Comment

by:sedgwick
Comment Utility
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
Comment Utility
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
Comment Utility
 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
Comment Utility
Thats what I was looking for....Thanks sedgwick
0
 

Author Comment

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

Expert Comment

by:sedgwick
Comment Utility
Post the url here
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 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

11 Experts available now in Live!

Get 1:1 Help Now