C# Return Multiple Values from SQL Query

Hi Experts,
I have the following SQL query where I need to assign the returned values to some variables.

SqlCommand cmdSendRequest = new SqlCommand("SELECT rtrim(PHONE1),"
            + "rtrim(ADDRESS1),rtrim(CITY),rtrim(STATE),rtrim(ZIP) FROM RM00101"
            + " WHERE CUSTNMBR ='" + txtCustNum.Text + "'", con);

Here are the variables I need to assign the values to.

hPhone = ["PHONE1"].ToString();
addrs1 = ["ADDRESS1"].ToString();
city = ["CITY"].ToString();
state = ["STATE"].ToString();
zip = ["ZIP"].ToString();

Of course, the code above where I am assigning a value to the variable isn't working.  I tried using cmdSendRequest.ExecuteScalar(); , but that is for returning only a single value.  I also tried using DataReader, but couldn't get that to work either.  My application kept crashing as soon as it tried to assign a value to the first variable.  I tried using a Try/Catch to see why it was crashing, but no error was writted to the console.

Please, what is my best route to get the query result assigned to my variables?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

using DataReader:

SqlDataReader reader = cmdSendResult.ExecuteReader();

if (reader.Read()) // or while
hPhone = reader["PHONE1"].ToString();
addrs1 = reader["ADDRESS1"].ToString();
city = reader["CITY"].ToString();
state =reader ["STATE"].ToString();
zip = reader["ZIP"].ToString();

Of course you need to deal with null by your own.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shaun KlineLead Software EngineerCommented:
Assuming you are using SQL Server as your database, your query will not bring back columns with the names Phone1, Address1, City, State and Zip because you are using the RTRIM function on them. You should provide aliases for these columns:
SELECT rtrim(PHONE1) As Phone1, rtrim(ADDRESS1) As Address1 ,rtrim(CITY) As City ,rtrim(STATE) As State,rtrim(ZIP) As Zip FROM RM00101

For returning a dataset from a database, a datareader is general the best approach with the least overhead (as compared to using a data adapter with a data set). When using the data reader, use:
if(datareader.Read()) // This moved the data reader's cursor to the next available row of data, and assumes you only want to only handle a single row of data.
hPhone = datareader.["PHONE1"].ToString();
addrs1 = datareader.["ADDRESS1"].ToString();
city = datareader.["CITY"].ToString();
state = datareader.["STATE"].ToString();
zip = datareader.["ZIP"].ToString();

This code is generic by should give you the general idea.
isda-donAuthor Commented:
Both experts provided solutions on assigning values to the variables.  Assigning aliases for the column names completed the solution.  I feel it's only fair to split the points.  Thanks Experts.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.