Storing more than one value from the same column as a varible. Sql to C#

Question

4.00/5 (1 vote)

See more:

Hi, I am currently trying to read in multiple values from the same column in a data base but at diffrent rows.

Below is code

string AlphaX = null, AlphaY = null, BetaX = null, BetaY = null;

               SqlCommand userCommand = userConnection.CreateCommand();

               userCommand.CommandText = "SELECT X, Y FROM [dbo].[Coordinates] WHERE GroupName='Alpha'";

               SqlDataReader reader = userCommand.ExecuteReader();

               while (reader.Read())
               {
                   AlphaX = reader["X"].ToString();
                   AlphaY = reader["Y"].ToString();

                   MessageBox.Show(AlphaX);
                   MessageBox.Show(AlphaY);
               }

I want to be able to store the X and Y values of GroupName 'Beta' next. Do I have to close this connection after having stored the value and do another one or am I missing something?.

Posted 14-Mar-11 2:15am

WurmInfinity

Add a Solution

2 solutions

Solution 1

No, you can do a second SqlCommand on the same connection.

Posted 14-Mar-11 2:21am

OriginalGriff

Comments

WurmInfinity 14-Mar-11 8:25am

Ok. Thx

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christoph Keller · Accepted Answer · 2011-03-14T02:32:00

Solution 2

Hi,

Why you do not execute a general Query like SELECT X, Y, GroupName FROM [dbo].Coordinates], after that, split the query according the GroupName:

C#

while (reader.Read())
{
    string currentGroupName = reader["GroupName"].ToString();
    
    switch (currentGroupName)
    {
        case "Alpha":
            AlphaX = reader["X"].ToString();
            AlphaY = reader["Y"].ToString();
            break;
        case "Beta":
            BetaX = reader["X"].ToString();
            BetaY = reader["Y"].ToString();
            break;
    }
}

In this case you only execute one query and you could close the connection faster than querying the DB twice.

Hope this helps.

Best regards and have a nice day,
Stops

Posted 14-Mar-11 2:32am

Christoph Keller

Comments

WurmInfinity 14-Mar-11 8:33am

Ok thats wicked. Thanks :D!!!

Christoph Keller 14-Mar-11 8:34am

You're welcome! Glad to hear that it may help :)

WurmInfinity 14-Mar-11 11:01am

Ok unfortunatly im not very good with switch statements. The code compiles fine and I understand the logic however, it just wont store the information in the variables for some reason. Any suggestions?

Christoph Keller 14-Mar-11 11:07am

Is it possible that this is a casing problem?
Try to .ToLower() the currentGroupName and write all cases in lower cases.

Also you could set a breakpoint at the switch-statement to see which groupName you got currently. Perhaps there you see what is going wrong.

Hope this helps.