CODEWITHSUNDEEP
c#mysql.net
bramhaag
bramhaag

Reputation: 135

C# - MySQL - Get value from column if status is 'a'

I'm trying to get a value from 123 if the status is a/ This is what my table looks like 

|123|status|
|dsg|a     |
|ert|b     |
|abc|a     |

So, I only want to get dsg and abc, because there status is a.

But how can I do this in C#?

This is my current code:

public string getAll() {
    command6 = new MySqlCommand("SELECT 123 FROM table1 WHERE status= ?status", connection);
    command6.Prepare();

    command6.Parameters.AddWithValue("?status", "a");
    command6.ExecuteNonQuery();


    return command6.ExecuteScalar().ToString();
}

But this only returns dsg, not dsg and abc :(

Upvotes: 0

Views: 154

Answers (2)

Zaheer Ahmed
Zaheer Ahmed

Reputation: 28588

You are using ExecuteScalar which return only first record and ignore others as msdn say:

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

You need to use:

MySqlDataReader dr = command6.ExecuteReader();
List<string> data=new List<string>();
while (dr.Read())
{
    data.Add(dr["123"].ToString());        
}

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You get only one value because you're using ExecuteScalar() method which is used to return one value only (in your case the value of the first record returned).

You need to use a reader to return multiple values:

 MySqlDataReader reader = command6.ExecuteReader();
 while(reader.Read())
 {
     String value = reader.GetString(0); // do something with it, e.g. put into a list
 }

You need to also change the output type of your function getAll() to return multiple string values (e.g. an array or list) or concatenate all the values from the database into one string.

Upvotes: 1

Related Questions