0

I have a check box list in ASP.NET with some values and I want to pass all of those values selected into a SQL parameter. The reason for it is so the user can display which columns from a table they want to see.

<asp:CheckBoxList ID="CheckBoxList1" runat="server" RepeatColumns="5" RepeatDirection="Horizontal" Width="100%">
            <asp:ListItem Value="[ID]">ID</asp:ListItem>
            <asp:ListItem Value="[USER]">User</asp:ListItem>
            <asp:ListItem Value="[City]">City</asp:ListItem>
</asp:CheckBoxList>

Now when the user clicks on the view button, I run this code to get the list of columns the user wants to see and I place it in the label to make sure I can see visually it's working correctly. Which is does and it displays it as "[ID], [User], [City]".

string listCheckBox = "";
foreach (ListItem li in CheckBoxList1.Items)
{
    if (li.Selected)
    {
        listCheckBox += li.Value + ",";
    }
}

listCheckBox = listCheckBox.Remove(listCheckBox.Length - 1);
Label1.Text = listCheckBox;

But my issue is that when I jump to my SQL Server Management Studio and view the list that's been passed in, I can only see the first column name? which in this case is "[ID]". 

DECLARE @SQL NVARCHAR(Max) = ''

    SET @SQL = 
    '
    SELECT ' + @TableList + ' FROM People
    '

EXEC( @SQL)

I do believe it has something to do with the SQL Parameters in Visual Studio which might remove the rest of the list. Below is the ASP code...

if (Page.IsValid)
{
    string CS = ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand cmd = new SqlCommand("GetColumnListNames", con);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter TableList = new SqlParameter("@TableList", listCheckBox);
        cmd.Parameters.Add(TableList);

        con.Open();

I'm really not sure why it isn't taken in all the values. I think it has something to do with the SqlParameter in Visual Studio, maybe it's cutting it off?

Any thoughts anyone?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Try the following for passing in your parameter instead of `AddWithValue`: `cmd.Parameters.Add("@TableList", SqlDbType.VarChar, 1000).Value = listCheckBox;` – Igor Oct 10 '18 at 18:13
  • Also verify the value of the parameter being passed in to Sql Server and what it does with it by running Sql Server Profiler at the time the command is executed. – Igor Oct 10 '18 at 18:14
  • Can you show the declaration for variable `@TableList` in the stored procedure? – Aman B Oct 10 '18 at 18:36
  • Possible duplicate of [Can I pass column name as input parameter in SQL stored Procedure](https://stackoverflow.com/questions/10092869/can-i-pass-column-name-as-input-parameter-in-sql-stored-procedure) – Jon P Oct 10 '18 at 23:16
  • While the flagged duplicate is for Stored Procedures, the same principles apply. The short answer is you can't pass column names as parameters. Unless we're talking **massive** amounts of data, return all valid columns and handle the what is displayed in the code behind. – Jon P Oct 10 '18 at 23:17

0 Answers0