Return multiple values from sql to label

I have the label:

<asp:Label ID="lbl1" runat="server"></asp:Label>

Code behind:

protected void Page_Load(object sender, EventArgs e)
{
    lbl1.Text = ImageCheck().ToString();
}

And:

protected int ImageCheck()
{
    SqlConnection connection = new SqlConnection(@"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|***.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
    string CommandText2 = "SELECT * FROM Machreta WHERE noImage = 1";
    SqlCommand command2 = new SqlCommand(CommandText2, connection);
    connection.Open();
    int check = (int)command2.ExecuteScalar();
    connection.Close();

    return check;
}

How can i return multiple values? That label display only single value but there are 6 more in the table.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

try this:

    protected string ImageCheck()
    {

      var result = new StringBuilder();

    using(var connection = new SqlConnection(@"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|***.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"))
    {
        string CommandText2 = "SELECT * FROM Machreta WHERE noImage = 1";
        SqlCommand command2 = new SqlCommand(CommandText2, connection);
        connection.Open();

      using(var reader = command2.ExecuteReader())
      {
        while (reader.Read())
        {
          result.Append(reader.GetString(0));
        }
      }

      return result.ToString();

    }
 }

of course is only an example and not fully solving your issue but should be a starting point 🙂

Method 2

Here is the explanation of ExecuteScalar() method. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.71%29.aspx

“Executes the query, and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.”

Also, SELECT * will fetch all the columns. You probably want to display multiple values for single column. Then select the column name in select statement.

SELECT xyzColumn FROM Machreta WHERE noImage = 1

Lastly, you can assign only one string to label.text. So, you will have to concatenate all these strings (multiple values for single column) and then assign it to label text. Use a reader and ExecuteReader() method instead of ExuecuteScalar().

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0