C# SQL select statement - how do I change value pulled?

Question

0.00/5 (No votes)

See more:

Hello All -

I hope this is the correct forum to ask this. I am using the below code to pull values from SQL into a datagridview. Everything works well, except I would like the "Value" cell (which will either come back as 1, 2, or 3) to change when the results are queried to Started, Stopped, Stopping. Also, if the Value number is not 1, 2, or 3 then I would like to have the datagridview say "Unknown"

So, for example, in my table I could have:
FirstName = Mike
LastName = Jones
Value = 1

FirstName = Steve
LastName = McDanields
Value = 3

FirstName = Chad
LastName = Smith
Value = 0

What I would like the datagridveiw to show is:

FirstName = Mike
LastName = Jones
Value = Started

FirstName = Steve
LastName = McDanields
Value = Stopping

FirstName = Chad
LastName = Smith
Value = Unknown

Code I am using:

C#

SqlConnection myConnection = new SqlConnection("Server=mylaptop; Database = users; User Id = test; Password = testpass;");


myConnection.Open();
           
SqlCommand objcmd = new SqlCommand("SELECT LastName, FirstName, Value, FROM table1 ", myConnection);

     
SqlDataAdapter adp = new SqlDataAdapter(objcmd);
DataTable dt = new DataTable();

adp.Fill(dt);
dataGridView1.DataSource = dt;

Hope someone can help and thank you in advanced.

What I have tried:

Google, Placing if then statements in code, but cannot figure out how to do it.

Posted 4-Sep-16 8:15am

Member 12719658

Updated 4-Sep-16 8:52am

Add a Solution

2 solutions

Solution 1

You can set the value from sql using case statement such as

SQL

SELECT 
LastName, 
FirstName, 
( case Value 
   when 1 then 'Started'
   when 2 then 'Stopped'
   when 3 then 'Stopping'
   else 'Unknown'
end ) as Status
FROM table1

Note that the case statement can be used for more complex sql

Posted 4-Sep-16 8:42am

P_Z

Comments

Maciej Los 4-Sep-16 14:43pm

5ed!

Member 12719658 4-Sep-16 14:50pm

So, with your suggestion, it should look like this?

SqlCommand objcmd = new SqlCommand("SELECT LastName, FirstName, ( case Value
when 1 then 'Started'
when 2 then 'Stopped'
when 3 then 'Stopping'
else 'Unknown'
end )as Status, FROM table1", myConnection);

Keeps saying error on the Fill(dt).

Member 12719658 4-Sep-16 21:35pm

Thank you for the quick replies. I found out that the column "Value" is a tinyint datatype, which is causing issues while trying to run the statement. I am trying to come up with a way to copy the table to another table then run it - Like in P_Z reply. Unless you have any suggestions?

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)

Maciej Los · Accepted Answer · 2016-09-04T08:52:00

Solution 2

In addition to solution 1 by P_Z[^], i'd suggest to create helper table, for example:

SQL

--create variable, type of Table
DECLARE @tmp TABLE([Value] INT, [Description] VARCHAR(150))

INSERT INOT @tmp ([Value], [Description])
VALUES(0, 'Unknow'), (1, 'Started'), (3, 'Stopped')

SELECT t1.LastName, t1.FirstName, t2.[Description]
FROM table1 t1 INNER JOIN @tmp t2 ON t1.[Value] = t2.[Value]

Note that, you can create real table.

Note #2 - remove last comma, just before FROM statement.

SQL

SELECT LastName, FirstName, Value, FROM table1

For further details about joining the tables, please see: Visual Representation of SQL Joins[^]

Posted 4-Sep-16 8:52am

Maciej Los

Updated 4-Sep-16 9:35am

v2

Comments

P_Z 4-Sep-16 15:11pm

Actually I think the tmp table should be used to normalize the tables right?

Thanks for the 5*, your solution also makes sense

Maciej Los 4-Sep-16 15:43pm

Thank you.

Member 12719658 4-Sep-16 22:23pm

Thanks P_Z. I accepted yours as the Best Solution because I like your idea of creating a temp table to do this. Now, I will need to search on how to put this into my original code. :)

Thanks again P_Z. I figured out how I need to do this. This was awesome and very fast to get a response. This can be closed as I am using your suggestion. :)

Maciej Los 5-Sep-16 1:34am

Member 12719658, i'm not P_Z...
Thank you, anyway.