Parameratized Database Query Issue
===

I have created the query listed below. It works when I run it. 

    string sortDir = (this.GridSortDirection == SortDirection.Descending ? " DESC" : " ASC");
    int startIndex = 1;
    int endIndex = this.gvData.PageSize;
    
    
    SELECT RowNum, [ID], [Name], [Description], [DisplayIndex], [Status] 
    FROM
    (
    	SELECT  [ID], [Name], [Description], [DisplayIndex], CASE WHEN Status = 1 THEN 'Active' ELSE 'Disabled' END AS [Status] ,
    	ROW_NUMBER() OVER(ORDER BY [" + this.GridSortExpression + "]" + " " + sortDir + @")as 'RowNum'
    	FROM  [MyDatabase].[dbo].[t_MyTable] s
    				
    ) as Info
    WHERE RowNum BETWEEN " + startIndex.ToString() + " AND " + endIndex.ToString()

I attempted to restructure it to parameratized query format as shown below, but am getting an error when it runs. The error states that there is a syntax error near sortDir.

    string sql = @"SELECT RowNum, [ID], [Name], [Description], [DisplayIndex], [Status] 
    FROM
    (
    	SELECT  [ID], [Name], [Description], [DisplayIndex], CASE WHEN Status = 1 THEN 'Active' ELSE 'Disabled' END AS [Status] ,
    	ROW_NUMBER() OVER(ORDER BY @SortExpression @SortDir)as 'RowNum'
    	FROM  [MyDatabase].[dbo].[t_MyTable] s
    				
    ) as Info
    WHERE RowNum BETWEEN @startIndex AND @endIndex";
    
    
    cmd = new SqlCommand(sql, conn);
    cmd.Parameters.AddWithValue("@SortExpression", this.GridSortExpression);   
    cmd.Parameters.AddWithValue("@SortDir", sortDir);                 
    cmd.Parameters.AddWithValue("@startIndex", startIndex);
    cmd.Parameters.AddWithValue("@endIndex", endIndex);
    da = new SqlDataAdapter(cmd);
    da.Fill(dt);

I also tried the following to no avail... same error message

    cmd = new SqlCommand(sql, conn);
    cmd.Parameters.AddWithValue("@SortExpression", this.GridSortExpression + " " + sortDir);                    
    cmd.Parameters.AddWithValue("@startIndex", startIndex);
    cmd.Parameters.AddWithValue("@endIndex", endIndex);
    da = new SqlDataAdapter(cmd);
    da.Fill(dt);

Can anyone refer me to my error?
Many thanks in advance


