none
Cannot perform '=' operation on System.String and System.Int32

    Question

  • Hello

    I've built a SharePoint feature that retrieves a recordset into a DataTable from a database. To get a specific row in the recordset I perform a DataTable.Select(filterExpression). Everything works fine in my development environment, but when the feature runs on production, I get the exception in the title. The expression simply looks like this:

    string keyField = "ProjNr"; //ProjNr is a columnnamn in the database
    
    string specificProjectNumber = 123456;
    DataTable.Select(keyField + " = " + specificProjectNumber)
    
    

     

    What could cause this? My first thought was to wrap the keyfield in apostrophes, but then again, it does work in development. So I'm thinking there's a difference in the environment, like version of SQL database, collation or whatever it could be. I don't know so that 's why I'm asking :) any ideas?


    Studying system development. I always give credit to helpful posts!

    Thursday, April 01, 2010 10:07 AM

Answers

  • Hi, all!

    @Naom: The select method WILL FAIL because DataTable.Select method does not support cast function, but convert function is here, its syntax is:

    Select("convert(col1, 'System.Int32') = 111")

    I made a simple experiment to reproduce an error:

    System.Data.DataTable t = new System.Data.DataTable();
    t.Columns.Add("col1", typeof(string));
    
    System.Data.DataRow r;
    r = t.NewRow();
    r["col1"] = "111";
    t.Rows.Add(r);
    r = t.NewRow();
    r["col1"] = "abc";
    t.Rows.Add(r);
    foreach (var r1 in t.Select("col1 = 111"))
    	Console.WriteLine(r1["col1"].ToString());
    

    Above sample throws the same exception: Cannot perform '=' operation on System.String and System.Int32.

    This indirectly confirms that column ProjNr has type of System.String and correct code would look like in my previous post:

    string keyField = "ProjNr"; //ProjNr is a columnnamn in the database
    
    string specificProjectNumber = 123456;
    DataTable.Select(keyField + " = '" + specificProjectNumber + "'")
    
    • Proposed as answer by Dmitry Yudin Wednesday, April 07, 2010 10:32 AM
    • Marked as answer by Rex E Friday, April 09, 2010 7:15 PM
    Wednesday, April 07, 2010 10:31 AM

All replies

  • Hello

    I've built a SharePoint feature that retrieves a recordset into a DataTable from a database. To get a specific row in the recordset I perform a DataTable.Select(filterExpression). Everything works fine in my development environment, but when the feature runs on production, I get the exception in the title. The expression simply looks like this:

    string keyField = "ProjNr"; //ProjNr is a columnnamn in the database
    
    
    
    string specificProjectNumber = 123456;
    
    DataTable.Select(keyField + " = " + specificProjectNumber)
    
    
    
    

     

    What could cause this? My first thought was to wrap the keyfield in apostrophes, but then again, it does work in development. So I'm thinking there's a difference in the environment, like version of SQL database, collation or whatever it could be. I don't know so that 's why I'm asking :) any ideas?


    Studying system development. I always give credit to helpful posts!

     


    --try this

    string sql="keyField  like 'specificProjectNumber'";

    DataTable.Select(sql)


    Life is a race,tez nahi bhagoge to log kuchalke agey niklenge!
    Thursday, April 01, 2010 1:37 PM
  • Is your KeyField int or character? Also, how this works


    string specificProjectNumber = 123456;

    I think it should be

    string specificProjectNumber = "123456";

    Also, are both databases identical?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Rajendra Kumbar Thursday, April 01, 2010 1:44 PM
    • Marked as answer by Zongqing Li Wednesday, April 07, 2010 7:16 AM
    • Unmarked as answer by Rex E Friday, April 09, 2010 7:12 PM
    Thursday, April 01, 2010 1:42 PM
    Moderator
  • Hi, thanks for replying guys.

    @Rajendra: The SQL statement shouldn't be a problem as it's working on the dev environment. And I don't want to use LIKE as the comparison needs to be exact.

    @Naom: The databases are identical in means of table setup. I don't know about the production SQL Server version etc. And yes, it was supposed to be "123456";. The KeyField is typed as int.


    Studying system development. I always give credit to helpful posts!
    Tuesday, April 06, 2010 8:42 AM
  • DataTable is .NET object and has nothing in common with transact sql.

    What kind of exception you get? What its message is saying?

    Tuesday, April 06, 2010 9:24 AM
  • Hi Dmitry, the error message is in the title.
    Studying system development. I always give credit to helpful posts!
    Tuesday, April 06, 2010 9:26 AM
  • Sorry I didn't understand that.

    To verify data type of column you can try this:

    DataTable.Columns[keyField].DataType

    My guess is that ProjNr is the column of type string.

    string keyField = "ProjNr"; //ProjNr is a columnnamn in the database
    
    string specificProjectNumber = 123456;
    DataTable.Select(keyField + " = '" + specificProjectNumber + "'")
    
    Tuesday, April 06, 2010 9:40 AM
  • Looks like it still tries to do some sort of the conversion.

    Try

    string specificProjectNumber = "123456";
    DataTable.Select(keyField + " = cast(" + specificProjectNumber + " as int)")


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 06, 2010 1:43 PM
    Moderator
  • Hi, all!

    @Naom: The select method WILL FAIL because DataTable.Select method does not support cast function, but convert function is here, its syntax is:

    Select("convert(col1, 'System.Int32') = 111")

    I made a simple experiment to reproduce an error:

    System.Data.DataTable t = new System.Data.DataTable();
    t.Columns.Add("col1", typeof(string));
    
    System.Data.DataRow r;
    r = t.NewRow();
    r["col1"] = "111";
    t.Rows.Add(r);
    r = t.NewRow();
    r["col1"] = "abc";
    t.Rows.Add(r);
    foreach (var r1 in t.Select("col1 = 111"))
    	Console.WriteLine(r1["col1"].ToString());
    

    Above sample throws the same exception: Cannot perform '=' operation on System.String and System.Int32.

    This indirectly confirms that column ProjNr has type of System.String and correct code would look like in my previous post:

    string keyField = "ProjNr"; //ProjNr is a columnnamn in the database
    
    string specificProjectNumber = 123456;
    DataTable.Select(keyField + " = '" + specificProjectNumber + "'")
    
    • Proposed as answer by Dmitry Yudin Wednesday, April 07, 2010 10:32 AM
    • Marked as answer by Rex E Friday, April 09, 2010 7:15 PM
    Wednesday, April 07, 2010 10:31 AM
  • Your solution does work Dmitry, so I'll settle with your post as the answer. Although this still doesn't explain why it worked in development to begin with.

     


    Studying system development. I always give credit to helpful posts!
    Friday, April 09, 2010 7:18 PM
  • Could it be that the KeyField is really a character and not int as you thought?

    Also, perhaps it's integer in the database, but a String type in the DataSet?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Friday, April 09, 2010 7:21 PM
    Moderator
  • The only thing I can suppose is that the column ProjNr is defined differently in your development and production systems.
    Monday, April 12, 2010 6:35 AM
  • Thankyou it worked well.
    Thursday, March 31, 2011 7:45 AM