C# - Issue with DataReader Elements

Question

4.00/5 (1 vote)

See more:

Hi everyone!

Can someone show me what I'm doing wrong?

This is my current code segment.

C#

foreach (string cla in cc_list_array)
{
                writer.WriteElementString("caption", cla);

                SqlCeCommand cla_filenames = new SqlCeCommand("SELECT MIN(file_name) FROM waveform_files WHERE file_name LIKE ' %" + cla + "% ' ", conn);
                cla_filenames.CommandType = CommandType.Text;
                SqlCeDataReader cla_filenames_dr = cla_filenames.ExecuteReader();

                while (cla_filenames_dr.Read())
                {
                    if (! DBNull.Value.Equals(cla_filenames_dr.GetString(0)))
                    {
                        MessageBox.Show(cla_filenames_dr.GetString(0));
                    }
                }                          
}

My troubleshooting check is the while statement to make sure that there are elements in the datareader so I can manipulate them accordingly.

I am receiving this error:
The column at the specified ordinal (0) contains DBNull value which can not be represented as a built-in .NET system type. Use SqlTypes or IsDBNull property.
(This is on the if (! DBNull.Value.Equals(cla_filenames_dr.GetString(0))) line).

When I run the query on the individual names in sql server itself, I get results everytime. The problem lies within when I do the full LIKE statement on all of the records.

Thanks in advance for any assistance!

Posted 19-Oct-12 6:10am

joshrduncan2012

Updated 19-Oct-12 6:12am

Kenneth Haugland

v2

Add a Solution

2 solutions

Add a Solution

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

Rohit Shrivastava · Answer 1 · 2012-10-19T08:18:00

You have to use for IsDBNull or DBNull.Value.

if (! DBNull.Value.Equals(cla_filenames_dr.GetString(0)))

should be changed to

if (! DBNull.Value.Equals(cla_filenames_dr[0]))

or

if (! cla_filenames_dr.IsDBNull(0))

Whenever you try to call GetString, it is expected to get a string but if it is null then system is not able to convert DBNull to string which is the reason for the issue.

OriginalGriff · Answer 2 · 2012-10-19T08:27:00

I agreed with Rohit that the root cause of your problem is the DBNull - as your error message clearly states.
When you run this query in SSMS it handles the display of the null itself, so you never have to worry about it. But when you code it, you have to handle the return value yourself, and DBNull is a specific value which does not work "nicely" with .NET. I woudl hcange hwat Rohit suggests slightly:

C#

while (cla_filenames_dr.Read())
{
    object o = cla_fileName_dr[0];

    if (o != DBNull.Value)
    {
        MessageBox.Show((string) o);
    }

In addition I would strongly recommend that you use a using block on your reader:

C#

SqlCeCommand cla_filenames = new SqlCeCommand("SELECT MIN(file_name) FROM waveform_files WHERE file_name LIKE ' %" + cla + "% ' ", conn);
cla_filenames.CommandType = CommandType.Text;
using (SqlCeDataReader cla_filenames_dr = cla_filenames.ExecuteReader())
{
    while (cla_filenames_dr.Read())
    {
        object o = cla_fileName_dr[0];

        if (o != DBNull.Value)
        {
            MessageBox.Show((string) o);
        }
    }
}

Or you may find you have problems as the number of items in cc_list_array increases.

C# - Issue with DataReader Elements

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

C# - Issue with DataReader Elements

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us