locked
Sorting SQL Adapter RRS feed

  • 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)

    Sunday, April 18, 2021 12:27 AM

All replies

  • 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

    Sunday, April 18, 2021 10:03 AM
  • 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"

    Monday, April 19, 2021 8:47 AM
  • 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.

    Monday, April 19, 2021 9:08 AM