asp.net Database return only one value instead of whole set

Question

I'm trying to grab information about a particular user from my db:

 public string GetUserData()
    {
        string conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString))
        {

            SqlCommand com = new SqlCommand("SELECT lastName , phoneNo , creditCardNo , dateOfBirth  FROM UserExtendedDataSet WHERE UserId = @UserId", con);

            com.Parameters.Add("@UserId", SqlDbType.NVarChar).Value = getUserId();

            con.Open();

            string result = Convert.ToString(com.ExecuteScalar());
            StatusLabel.Text = result;
            return result;
        }
    }

The problem is that it returns only the first table data (in this case lastName), i know that I can write separate queries for each field but I assume this would not be really efficient.

Is there anyway to get this data with one query ?

Steve · Accepted Answer · 2013-12-08 20:22:15Z

3

You need to call ExecuteReader on the command and use the instance of the SqlDataReader returned by the command. ExecuteScalar returns just the first column of the first row.

 using(SqlDataReader reader = com.ExecuteReader())
 {
     if(reader.Read()
     {
       StatusLabel.Text = reader[0].ToString() + " " + reader[1].ToString();
       .... other labels for other fields 
     }
 }

Instead the ExecuteReader moves on the first row (if there is one) after you call the Read method and then every column of the row is available from the reader as an indexed array. (Pay attention to null values before applying any conversion like ToString())

By the way, your tags says MySql but you use Sql Server classes in the namespace SqlClient?

edited Dec 8, 2013 at 20:22

answered Dec 8, 2013 at 20:17

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

molu2008 Over a year ago

Yep sorry for mish-mash my tag (autocomplete). Any way thanks very much Steve for help this solve my problem in 100%. +1

Rahul Tripathi · Accepted Answer · 2013-12-08 20:17:59Z

0

The reason is because you are only executing the query and ExecuteScalar returns the first row from the database.

string result = Convert.ToString(com.ExecuteScalar());

You may try to use ExecuteReader instead like this:

using(SqlDataReader reader = com.ExecuteReader())
 {
     if(reader.Read())
     {
       //your code here
     }
 }

The MSDN says that ExecuteScalar method:

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.

edited Dec 8, 2013 at 20:17

answered Dec 8, 2013 at 20:12

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Collectives™ on Stack Overflow

asp.net Database return only one value instead of whole set

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related