Select query that should return multiple rows: It does not

[weird0]

The given should return A,B that is two rows because both
accountnames A and B have the same user_Id=1 but it is only returning
A when I performed the op. MessageBox.Show("AccountNames:" +
Object.ToString() );

[WebMethod]
public Object GetAccountNames(string User_Id)
{
//string AccountNames;
Object AccountNames;

// create a Command,Connection and Reader object

System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(connectionString);
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand();
//Object returnValue;

// Search for the pincode

cmd.CommandText = "SELECT acc_name FROM Account_Information
where user_id='" + User_Id + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();
AccountNames = cmd.ExecuteScalar();
//AccountNames = returnValue.ToString();
sqlConnection1.Close();

return AccountNames;

}

 

[sloan]

ExecuteScalar is the wrong choice here.

You want

..ExecuteReader

or

LoadDataSet


ExecuteScalar is for 1 single value.

like

Select count(*) as MyCount from dbo.Emp

would be a single value. ExecuteScalar is for that kind of purpose.

 

[sloan]

I might have misread.

Are you expecting one and only one value?

See here:

http://msdn2.microsoft.com/en-us/library/ms978510.aspx
and scroll down to the appendix
Appendix
How to Enable Object Construction for a .NET Class
keep scrolling down a little, basically "every scenario" is listed there.

1 row, multiple columns
1 row, 1 value (what you want)
multi rows, multi columns

ExecuteScalar is the wrong choice here.

You want

.ExecuteReader

or

LoadDataSet


ExecuteScalar is for 1 single value.

like

Select count(*) as MyCount from dbo.Emp

would be a single value. ExecuteScalar is for that kind of purpose.

The given should return A,B that is two rows because both
accountnames A and B have the same user_Id=1 but it is only returning
A when I performed the op. MessageBox.Show("AccountNames:" +
Object.ToString() );

[WebMethod]
public Object GetAccountNames(string User_Id)
{
//string AccountNames;
Object AccountNames;

// create a Command,Connection and Reader object

System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(connectionString);
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand();
//Object returnValue;

// Search for the pincode

cmd.CommandText = "SELECT acc_name FROM Account_Information
where user_id='" + User_Id + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();
AccountNames = cmd.ExecuteScalar();
//AccountNames = returnValue.ToString();
sqlConnection1.Close();

return AccountNames;

}

 

[Guest]

In addition to what Sloan pointed out, you really want to get into the habit
of using parmeterized queries or even better, stored procedures.
Peter