In asp.net, I want to select the Top x from a sql server database where x is set programattically in the code behind (cs).
Data binding a string from the code behind to pass to the SelectCommand in the SqlDataSource control doesn't seem to be working.
Is there a way to bind a variable from the code to the SelectCommand of the aspx SqlDataSource, or is there another way?
Assuming you have a SQL data source declared in the front end.
<asp:SqlDataSource ID="SqlDataSource" runat="server">
</asp:SqlDataSource>
In the code behind of your page add to the appropriate event code something like this to programmatically set the SQL data source command.
string sTop = "10";
SqlDataSource.SelectCommand = "SELECT Top " + sTop + " * FROM dbo.clients";
Also, consider using a DataTable or DataSet instead.
you can do this:
begin
declare @top int
set @top = 2
SELECT top(@top) * FROM MyTable
end
EDIT Since you can use a variable with TOP you can send the paramater "@top"(int type) and use it like the above sql-code (only the select)...But your question already has been answered (see last answer).
Thanks to all. I learned a lot! After posting the question, I found these links in MSDN that showed me how to use SessionState and... the missing link... Parameters! I learned how to put them in the SqlDataControl for the SelectCommand to use.
https://msdn.microsoft.com/en-us/library/xt50s8kz(v=vs.140).aspx
https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sessionparameter(v=vs.140).aspx
My code-behind:
int count = 5;
Session["itemCount"] = count;
my apsx:
<SqlDataSource>
...
SelectCommand="SELECT TOP (@itemMax) * FROM TableName">
<SelectParameters>
<asp:SessionParameter Name="itemMax" SessionField="itemCount"/>
</SelectParameters>
</SqlDataSource>
Again, thanks to all. It was much appreciated and made my first forray into the Stack Overflow community a positive experience!
This is another way of solving your problem:
string cmdStr1 = "SELECT * FROM [Table1];";
DataSet ds = new DataSet();
try
{
using (SqlConnection conn1 = new SqlConnection(connStr))
{
using (SqlCommand cmd1 = new SqlCommand(cmdStr1, conn1))
{
conn1.Open();
using (SqlDataAdapter da = new SqlDataAdapter(cmd1))
{
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
conn1.Close();
cmd1.Dispose();
conn1.Dispose();
}
}
}
catch (Exception ex)
{
Label2.Text = ex.ToString();
You could use row_number() if you want to parameterize the number of rows to be selected.
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, column1, column2,column3 FROM tablename ) AS t
WHERE rownumber <= @topCount
SqlDataSource. You could do so in your code behind class. Initially, I suggest you try to find out how we can open a sql connection, query a table and consume the result,ADO.NET. Then using a parameter to your query string, you could achieve so fairly easily.