Null check from database

Question

0.00/5 (No votes)

See more:

I whant to check if a column in a database is null, If so

<pre lang="midl">dataContracteClienti.DefaultCellStyle.ForeColor = Color.Gray;
                    dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()));

If is not null:

dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()), (Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataAzi.ToString()) + 1) + Convert.ToDecimal(Row["Total_Platit"].ToString()), ((Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataLaTermen.ToString()) + 1)) + Convert.ToDecimal(Row["Total_Platit"].ToString()));

I try someting like this (full code):

dataContracteClienti.Rows.Clear();
           Program.Connection.CommandText = "select Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent, ContractItems.DeletedId AS ContracteActive, sum(ContractItems.Payment) AS Total_Platit, Now() AS DataCurenta from Contracts INNER JOIN ContractItems ON Contracts.ContractId = ContractItems.ContractId WHERE ClientID=@ClientID "
               + "GROUP BY Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent, ContractItems.DeletedId ORDER BY Contracts.StartDate DESC";
           Program.Connection.AddParameter("@ClientID", cboNumeClient.SelectedValue.ToString());
           DataTable Table = new DataTable();
           Program.Connection.FillDataTable(Table, true);


           foreach (DataRow Row in Table.Rows)
           {

               DiferentaData DiferentaDePlataLaTermen = new DiferentaData(Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDateTime(Row["StartDate"].ToString()));
               DiferentaData DiferentaDePlataAzi = new DiferentaData(Convert.ToDateTime(Row["DataCurenta"].ToString()), Convert.ToDateTime(Row["StartDate"].ToString()));

               if ( Convert.ToInt32(DiferentaDePlataAzi.ToString()) > Convert.ToInt32(DiferentaDePlataLaTermen.ToString()))
               {
                   dataContracteClienti.DefaultCellStyle.ForeColor = Color.OrangeRed;
                   dataContracteClienti.DefaultCellStyle.SelectionForeColor = Color.OrangeRed;
                   dataContracteClienti.DefaultCellStyle.SelectionBackColor = Color.LightBlue;
               }

               if (Row["ContracteActive"] == DBNull.Value)
               {
                   dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()), (Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataAzi.ToString()) + 1) + Convert.ToDecimal(Row["Total_Platit"].ToString()), ((Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataLaTermen.ToString()) + 1)) + Convert.ToDecimal(Row["Total_Platit"].ToString()));
               }
               else if (String.IsNullOrEmpty(Row["ContracteActive"].ToString()))
               {
                   dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()), (Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataAzi.ToString()) + 1) + Convert.ToDecimal(Row["Total_Platit"].ToString()), ((Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataLaTermen.ToString()) + 1)) + Convert.ToDecimal(Row["Total_Platit"].ToString()));
               }
               else
               {
                   dataContracteClienti.DefaultCellStyle.ForeColor = Color.Gray;
                   dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()));

               }

Exception from C#:

Input string was not in a correct format.Couldn't store <> in ContracteActive Column.  Expected type is Int32.

If i set the row in the database to Text is OK, if is Number don't work. Must be int, because is the ID from a nother table index.

Posted 28-Mar-11 3:52am

aciobanita

Updated 28-Mar-11 3:55am

v2

Add a Solution

2 solutions

Solution 1

Your question is not very clear. Are you saying the column "ContractActive" is text but you expected it to be int because it is a foreign key? How are the database tables constructed?

BTW, you should not be using inline SQL for this since it does not give SQL Server the chance to optimize and cache the executions.

Posted 28-Mar-11 4:02am

Not Active

Comments

aciobanita 28-Mar-11 10:16am

No "ContractActive" is INT in the database.

[no name] 28-Mar-11 10:37am

If it is an INT then this String.IsNullOrEmpty(Row["ContracteActive"].ToString() work not will it?

It seems you need more education before attempting to do this project, you are missing some core knowledge.

aciobanita 28-Mar-11 10:19am

im use procedure, NO?

Program.Connection.AddParameter("@ClientID", cboNumeClient.SelectedValue.ToString());

[no name] 28-Mar-11 10:35am

No you are not using a stored procedure, you are using a parameterized query. If you were then you would use Program.Connection.StoredProcedure rather than Program.Connection.CommandText. If you can't tell the difference then you really should not be developing anything.

aciobanita 28-Mar-11 10:43am

If you whant to help fine, in not...,
thanks.

[no name] 28-Mar-11 10:51am

If you don't understand the tools or concepts you won't be able to understand any help given.

aciobanita 28-Mar-11 10:56am

I know that, i'm new in C#, i search on the internet the difference between parameterized query and store procedure. I make a small program don't need to use stored procedure, i will not have a big database and for the moment don't need the selection to be very fast.
thanks.

[no name] 28-Mar-11 11:03am

Wrong answer. You should learn to do it correctly the first time, especially and most importantly since you are new. There is no difference between a small app and large app if you learn to do it right.

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)

Wendelius · Accepted Answer · 2011-03-28T05:54:00

Solution 2

Didn't go through all the code but if you want to check if a column has a value of NULL then it's best to use System.DbNull.Value in the comparison. For example:

If (row["ColumnName"] == System.DbNull.Value) ...

Posted 28-Mar-11 5:54am

Wendelius

Comments

[no name] 28-Mar-11 17:58pm

He is using DBNull.Value. The System you added in front is unnecessary since the using System; clause is included in every .NET code file.

The problem is trying to use an int column as a string

Wendelius 28-Mar-11 18:11pm

Ok. The point wasn't the System namespace but the DbNull usage. Obviously I got stuck to this: String.IsNullOrEmpty(Row["ContracteActive"].ToString()