21

I was trying to add data from a database to a ComboBox.

try
{
    SqlCeCommand com = new SqlCeCommand("select * from Category_Master", con);
    SqlCeDataReader dr = com.ExecuteReader();

    while(dr.Read())
    {
        string name = dr.GetString(1);
        cmbProductCategory.Items.Add(name);
    }
}
catch(Exception ex)
{
    System.Windows.Forms.MessageBox.Show(ex.Message, System.Windows.Forms.Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}

I get the following exception:

Unable to cast object of type 'System.Int32' to type 'System.String'

What am I missing here?

jordanz
  • 367
  • 4
  • 12
Kamal
  • 469
  • 2
  • 8
  • 19
  • 4
    What exception are you getting ?? try to use `Convert.ToString(value)` rather the `value.ToString()` – Ravi Oct 25 '13 at 06:08
  • Unable to cast object of type 'System.Int32' to type 'System.String'. – Kamal Oct 25 '13 at 06:09
  • I changed to dr.GetString(1).ToString(), but error still the same. – Kamal Oct 25 '13 at 06:11
  • @voo [`GetString()`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getstring.aspx) returns `string`. Don't need `.ToString()` here. – Soner Gönül Oct 25 '13 at 06:11
  • @KaMaLMoHaN On which exact line? What is the type of your second column? – Soner Gönül Oct 25 '13 at 06:12
  • 1
    Try selecting just the column you want, using `*` may very well end up getting the wrong column if someone changes the database schema later. I suspect you're not getting the column you're expecting, but an `int` column that is at that position. – Joachim Isaksson Oct 25 '13 at 06:13
  • 2
    Obviously, data reader's column with index 1 has a value, which type is `System.Int32`, not a `System.String`. – Dennis Oct 25 '13 at 06:13
  • I also changed to Convert.ToString(dr.GetString(1)); but no change.. – Kamal Oct 25 '13 at 06:13
  • @Soner Gönül in the databse? it is nvarchar. – Kamal Oct 25 '13 at 06:14
  • 3
    Why are you selecting *all* columns when you're only reading one of them? Just select the column you want - that will cut down on the possibilities of error to start with. – Jon Skeet Oct 25 '13 at 06:15

5 Answers5

31

Your column doesn't have the type string. Apparently it's int. So use:

dr.getInt32(1).ToString()

or even

dr.GetValue(1).ToString()

which should be more roubst to type changes in the database.

As some sort of general advice I try to follow at least:

  • Select only what you need. This has mostly performance reasons and the reason that you have to state the column names explicitly, thereby getting at least a sensible error if you change your schema incompatibly.

  • Access the fields using their names, e.g.

    dr.GetGuid(dr.GetOrdinal("id"))

    Such a thing can also be nicely solved by an extension method:

    public T GetFieldValue<T>(this DbDataReader reader, string columnName)
{
    return reader.GetFieldValue<T>(reader.GetOrdinal(columnName));
}

Side note: Including stack traces (or at least saying which line in your code the exception comes from) can be helpful to others trying to help you. As you can see from the wild guesses what the culprit could be. My guess would be that the stack trace looks somewhat like this:

SqlDataReader.GetString
YourCode.YourMethod

and that GetString looks more or less like this:

public string GetString(int index)
{
    return (string) GetValue(index);
}
grepsedawk
  • 3,324
  • 2
  • 26
  • 49
Joey
  • 344,408
  • 85
  • 689
  • 683
  • @AndreasNiedermair Yes it is _zero-based_. That's why, when you say `GetString(1)`, it search on second column in his table. – Soner Gönül Oct 25 '13 at 06:14
  • yes, first column is ID and second column is CategoryName which in nvarchar – Kamal Oct 25 '13 at 06:17
  • Exception is at this line, i checked. "string name = dr.GetString(1);" – Kamal Oct 25 '13 at 06:21
  • when you are talking about robustness, you should also consider a `reader.IsDBNull(int ordinal)`-check before accessing it with an ordinal! –  Oct 25 '13 at 06:24
  • you forgot to add the `` in your call of `reader.GetFieldValue` –  Oct 25 '13 at 06:26
  • Usually I would just assume that whoever uses the code would be able to fix such small problems, but given that they have problems with fixing `dr.["foo"]` already ... ah well. – Joey Oct 25 '13 at 06:27
  • 1
    ... ;) nevertheless, such small hints would vastly reduce the pitfalls, the OP might have to deal with ... –  Oct 25 '13 at 06:29
2

Your column doesn't seem to have type int. To avoid things like this, you can use the columnnames instead of indexes. 

try
{
    SqlCeCommand com = new SqlCeCommand("select * from Category_Master", con);
    SqlCeDataReader dr = com.ExecuteReader();
    while(dr.Read()){
        string name = dr["yourColumnName"].ToString();
        cmbProductCategory.Items.Add(name);
    }
}
catch(Exception ex)
{
    System.Windows.Forms.MessageBox.Show(ex.Message, System.Windows.Forms.Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Joey
  • 344,408
  • 85
  • 689
  • 683
Koen
  • 2,501
  • 1
  • 32
  • 43
  • that's not a valid reason!! with ordinal you can do typed things, like `GetString`, `GetInt32`... with the string-indexer you can access the `object` (untyped) value –  Oct 25 '13 at 06:17
  • Correct, but at least you know for sure which column you're addressing. – Koen Oct 25 '13 at 06:21
  • still no valid reason... `reader.GetOrdinal("myColumn")` gives an ordinal for the column i want to deal with - how can you be more explicit?! –  Oct 25 '13 at 06:28
  • @AndreasNiedermair Point taken. – Koen Oct 25 '13 at 06:32
2

you can try the following conversion:

  Convert.ToInt32(yourstring)
HibaHasan
  • 1,255
  • 2
  • 5
  • 12
0

Ok OK. Its solved....

Here is the code..

        try
        {
            SqlCeCommand com = new SqlCeCommand("select CategoryName from Category_Master", con);
            SqlCeDataReader dr = com.ExecuteReader();
            while(dr.Read()){
                string name = dr.GetString(0);
                cmbProductCategory.Items.Add(name);
            }
        }
        catch(Exception ex)
        {
            System.Windows.Forms.MessageBox.Show(ex.Message, System.Windows.Forms.Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

I changed the sqlcommand to a single value and changed column number for dr.getstring() to 0.. it worked. Thank you guys for the help.. I expect more because i am only half way in my project..

Kamal
  • 469
  • 2
  • 8
  • 19
0

Use Column name in your query then specify that column name into the reader.

SqlCeCommand com = new SqlCeCommand("select catg from Category_Master", con);
                SqlCeDataReader dr = com.ExecuteReader();
                while(dr.Read()){
                    string name = dr("catg").ToString();
                    cmbProductCategory.Items.Add(name);
                }
Sajid khan
  • 595
  • 5
  • 12