Click here to Skip to main content
15,877,711 members
Please Sign up or sign in to vote.
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
Updated 4-Sep-16 8:52am

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
 
Share this answer
 
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?
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[^]
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900