-2

I'm using a sql data source in asp.net, trying to have a variable here

SELECT TOP  @Number *
From Table

I want the variable to be from a dropdown that postsback on the same page. All the options in that dropdown are ints. How do I add the parameter? The DataSource.SelectParameters.Add() method only takes a string input

David Black
  • 1
  • 1
  • 2
    Side note - SQL query is somewhat suspicious - https://stackoverflow.com/questions/175962/dynamic-select-top-var-in-sql-server, but lack of code showing how you actually execute the query makes it hard to guess answer correctly... – Alexei Levenkov Dec 29 '17 at 04:52
  • Its not complicated. I jsut want it to be top4 or top 5 depending on the selection. Rest of the query works fine – David Black Dec 29 '17 at 04:53

1 Answers1

0

The DataSource.SelectParameters.Add() method only takes a string input?

No. You can provide any type of value and you have to use SQLDbType to specify parameter type.

Based on your comments to my answer.

var s = MealNumberDD.Text;

var param = new SQLParameter("MealNumber", SQLDbType.Int)
{
    Value = Convert.ToInt32(s)
};

RecipesDataSource.SelectParameters.Add(param); 
var query = "SELECT TOP (@MealNumber) * FROM Recipes"; 
RecipesDataSource.SelectCommand = query;
Nikhil Agrawal
  • 47,018
  • 22
  • 121
  • 208
  • I cant conver from sqlparameter to webcontrol.parameter – David Black Dec 29 '17 at 05:06
  • var param = new SqlParameter("MealNumber", Int32.Parse(MealNumberDD.SelectedValue)); RecipesDataSource.SelectParameters.Add(param); RecipesDataSource.SelectCommand = "SELECT TOP @MealNumber* FROM Recipes "; – David Black Dec 29 '17 at 05:10
  • Add () around parameter like `select (@MealNumber) from Recipes` . See my answer and let me know results. – Nikhil Agrawal Dec 29 '17 at 05:18
  • So we're ver close. this is my code so far: var s = MealNumberDD.Text; RecipesDataSource.SelectParameters.Add("MealNumber", DbType.Int32,s); var query = "SELECT TOP (@MealNumber)* FROM Recipes"; RecipesDataSource.SelectCommand = query; For some reason, when I put in s, the code runs, I select a number in the dropdown, and nothing happens. But when I put "3" instead of s as the parameter, it functions as it should – David Black Dec 29 '17 at 05:31
  • That means the problem is converting from `DropDown.Text` to `s`. It could be that when data does not populate in `s`, it is treated as 0 and no data is returned. – Nikhil Agrawal Dec 29 '17 at 06:03
  • that isnt the problem ive tried it. Ive gone through the debugger, its passing the information as expeceted – David Black Dec 29 '17 at 06:06
  • Don't use this way to pass parameter. Use like i have done in answer. – Nikhil Agrawal Dec 29 '17 at 06:10
  • In your way it is treated as `string` https://msdn.microsoft.com/en-us/library/cc491445(v=vs.110).aspx – Nikhil Agrawal Dec 29 '17 at 06:13