Erro wen sql server table has a empty value

NewToAspDotNet

Hi guys, I have a app that reads data from sqlserver table and set them into textboxes. The data type of my table's columns are 'text'. The problem is that wen there is a empty field in my table, i get the error: Unable to cast object of type 'System.DBNull' to type 'System.String' anyone a idea????

Pete OHanlon

You need to check it to see if it's a DBNull before you attempt to convert it. Suppose that you are using a DataReader, you could do this

if (!myDataReader.IsDBNull(0))
  myText = myDataReader.GetString(0);

Deja View - the feeling that you've seen this post before.

My blog | My articles

NewToAspDotNet

Pete O'Hanlon, Thanx for your response, bud what if i don't use a datareader? and have only a select. this how my code looks like: SqlCommand comm00 = new SqlCommand("select Cmp_nr from Companies where Cmp_name='" + companyName + "'", myconn); companyName = (string)comm00.ExecuteScalar();

Christian Graus

Depends where the code is. Obviously, whatever construct you're using ( data reader, data table, etc ), you're not checking if it has data in it before you try to read it.

Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

Malcolm Smart

Use the IsNull SQL Command and convert it on the server. Also, you should use parameters in your queries, not appending strings. Using parameters prevents your select statement being open to an SQL Injection attacks. (Said with a broad Scottish accent...)

"More functions should disregard input values and just return 12. It would make life easier." - comment posted on WTF

"This time yesterday, I still had 24 hours to meet the deadline I've just missed today."

echuck66

For simple select statements such as this, I would use ISNULL in the sql command... For example, using the code you included: SqlCommand comm00 = new SqlCommand(); string commSQL = "SELECT ISNULL(Cmp_nr, '') AS Cmp_nr FROM Companies where Cmp_name=@cmpName"; SqlPararameter cmpParam = new SqlParameter("@cmpName", SqlDbType.VarChar, 50); //note: 50 should be changed to the field length of your db field Cmp_name cmpParam.Value = companyName; comm00.Parameters.Add(cmpParam); comm00.Connection = myconn; comm00.CommandText = commSQL;

Luis Alonso Ramos

string companyName = "";
object result = comm00.ExecuteScalar();
if(result != DBNull.Value)
    companyName = (string) result;

That will work. Also, use parameterized queries. Your code has a security hole and is prone to SQL injection attacks. See this article[^].

Luis Alonso Ramos Intelectix Chihuahua, Mexico

My Blog!

Vasudevan Deepak Kumar

Djavid j wrote:

SqlCommand comm00 = new SqlCommand("select Cmp_nr from Companies where Cmp_name='" + companyName + "'", myconn);

This query, in short, is devil's palace. I mean it is subject to SQL Injection attacks. Did you consider having it as a Stored Procedure instead?

Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.