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

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 ?

Answers


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?


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.


Need Your Help

__cyg_profile_func_enter and g++ 2.95.4

c++ c linux gcc profiler

I want to get function's call stack of the programm with gcc option -finstrument-functions.

Why is C++ lambda implemented with functor instead of function pointer?

c++ lambda

I found out that lambdas in both MSVC and GCC are functors implementing an operator(). What is the reason they prefer functor to function pointers?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.