Sorting SQL Adapter

Question

User-148726852 posted

Hi,

I have this SQL adapter taking an id from a previous page but i want to be able to sort it by the Year field any ideas?

New SqlDataAdapter("SELECT Artist_Code, Artist, Title, Album_Code, Year, Tracks FROM tbl_Artist_Albums WHERE (Length=1 OR Length=2) AND Artist_Code=" + _
                Request.QueryString("id"), objConn)

Answer 1

User475983607 posted

Hi,

I have this SQL adapter taking an id from a previous page but i want to be able to sort it by the Year field any ideas?

New SqlDataAdapter(SELECT Artist_Code, Artist, Title, Album_Code, Year, Tracks FROM tbl_Artist_Albums WHERE (Length=1 OR Length=2) AND Artist_Code=" + _
                Request.QueryString("id"), objConn)

What exactly is the problem?  Is the a T-SQL syntax question?

"SELECT Artist_Code, Artist, Title, Album_Code, Year, Tracks FROM tbl_Artist_Albums WHERE (Length=1 OR Length=2) AND Artist_Code=" + _
                Request.QueryString("id") + " ORDER BY Year"

https://www.w3schools.com/sql/sql_orderby.asp

Your code is susceptible to SQL injection.   Consider writing a parameter query rather than using string concatenation.

https://forums.asp.net/t/1568268.aspx?SQL+Injection+And+Parameterized+Queries

Answer 2

User-148726852 posted

Thanks mgebhard. I'll convert it to a parameter query for security as because it allows the order by to work (I get an error message when i try to add the + " ORDER BY YEAR"

Answer 3

User753101303 posted

Hi,

The purpose of having error messages is to tell what is wrong rather than having to guess so please always tell which error message you have.

Try perhaps ORDER BY [Year] -- my guess is that your db could perhaps be case sensitive and/or SQL Server having an issue with YEAR being the name of an existing function ???

It could be also that you didn't keep the id causing to generate a WHERE Artist_Code= ORDER BY YEAR statement etc... Though using parameters is better it shouldn't cause directly this issue and you are heading to fix a problem withoout havinf learned about what caused it which is IMO a bad thing. Knowing what caused your problem coudl allow to avoid it again...

In short we have to guess which problem is more likely rather than just to see which problem you actually have.