none
what is this error: Invalid number of arguments: function convert().

    Question

  •  

    Dear All

    When I try to use convert function of MS sql server like this:

    String sql = " (employee_id = '" + employee_id + "') and ((convert(VARCHAR,w_date,103) = '" + w_date + "'))";

    PAYROLLDataSet.WORKINGTIMESRow[] workingtime_row = (PAYROLLDataSet.WORKINGTIMESRow[])pAYROLLDataSet.WORKINGTIMES.Select(sql);


    Whe I run, it rais bellow exception:

    Could any one tell me why. Thanks

    Invalid number of arguments: function convert().
    at System.Data.FunctionNode.Bind(DataTable table, List`1 list)
    at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
    at System.Data.UnaryNode.Bind(DataTable table, List`1 list)
    at System.Data.UnaryNode.Bind(DataTable table, List`1 list)
    at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
    at System.Data.DataExpression.Bind(DataTable table)
    at System.Data.DataExpression..ctor(DataTable table, String expression, Type type)
    at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
    at System.Data.DataTable.Select(String filterExpression)
    at payroll.FrmImport.updateOrAddWorkingTime(String employee_id, String w_date, String hours_early, String hours_late, String basic_hours, String extra_hours1, String extra_hours2) in D:\pnbaocuong\s3s.com.vn\Quan ly tien luong\source\payroll\payroll\FrmImpot.cs:line 255
    at payroll.FrmImport.AddPayrollFromExcel(String sttrFilePath) in D:\pnbaocuong\s3s.com.vn\Quan ly tien luong\source\payroll\payroll\FrmImpot.cs:line 86}
    Sunday, January 20, 2008 11:13 AM

Answers

  • You can't use SQL Convert function in Select method of DataTable class. Available Convert function which can be used is not from SQL but from .NET and have only two parameters. You can try something like this:

    String sql = "employee_id = " + employee_id + " AND w_date = '" + w_date + "'";


    pAYROLLDataSet.WORKINGTIMES.Locale = CultureInfo.CurrentCulture;

    PAYROLLDataSet.WORKINGTIMESRow[] workingtime_row = (PAYROLLDataSet.WORKINGTIMESRow[])pAYROLLDataSet.WORKINGTIMES.Select(sql);

     

    I'm not sure what type is employee_id but if it's string type then you will add single quotes to the sql expression. For datetime comparation, it is done as strings, so all you have to do is to adjust the string output from w_date variable. One way is to use ToString() with some format string in order to be equal to the date format of WORKINGTIMES table's Locale format, or to change the table's Locale to be the same as current culture so you will not have to use ToString().

    Sunday, January 20, 2008 3:13 PM
  • You can't use autogenerated FindBy'keycolumns' method because that is used for geting one row only. You must use query string something like this:

    String sql = "employee_id = " + employee_id + " AND w_date >= '" + w_date + "' AND w_date < '" + w_date.AddDays(1) + "'";

     

    This way of querying table for one day only rows is right way in SQL Server also, instead of using Convert or DateDiff.

     

    Sunday, January 20, 2008 3:57 PM

All replies

  • Hi,

    i guess that the third argument of the convert function is for datetime datatypes only. Try replacing the VARCHAR with datetime.

    Cheers
    Basti
    Sunday, January 20, 2008 11:55 AM
  •  

    Basti

    Thank for your replay,

     

    But I want to convert a datetime value to varchar, so what can I do.

    Sunday, January 20, 2008 12:06 PM
  • Try to convert the value twice:

    String sql = " (employee_id = '" + employee_id + "') and ((convert(VARCHAR, CONVERT(datetime, w_date,103)) = '" + w_date + "'))";

    Cheers
    Basti
    Sunday, January 20, 2008 12:17 PM
  • I have just try, but it still raise exception :

    System.Data.EvaluateException: Cannot find column [VARCHAR]

     

    Sunday, January 20, 2008 12:27 PM
  • Hi,

    i tried the query:

    SELECT w_date
    FROM test
    WHERE (CONVERT(VARCHAR, CONVERT(datetime, w_date, 103)) = w_date)

    and it worked for me. But if you just want to convert the datetime to a varchar you can use

    SELECT w_date
    FROM test
    WHERE (CONVERT(VARCHAR, w_date) = w_date)

    but this will not change the format of the datetime.

    Cheers
    Basti

    Sunday, January 20, 2008 12:41 PM
  • Yes, when I run that query in SQL Query Analyzer, it ok

    but when I use it in C# DataTable.select(" (CONVERT(VARCHAR, CONVERT(datetime, w_date, 103)) = w_date)") look like the code above, it raise error

    I don't know why?

    Sunday, January 20, 2008 1:25 PM
  • I never worked with the DataTable.select method. I always use a SQLDataReader or a SQLDataAdapter.
    You always execute complete sql statements there and it should work with one of these classes.

    using (SqlConnection conn = new SqlConnection(connString))
    {
       string sqlStatement = "SELECT * FROM test..WHERE CONVERT(.....";
       DataTable data = new DataTable();
       using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStatment, conn))
      {
         adapter.Fill(data);
      }
    }

    Cheers
    Basti
    Sunday, January 20, 2008 2:01 PM
  •  

    You right,

     

    I work with wizard mode, and I fell it is uncomfortable, maybe I do with your way

     

    Thanks

    Sunday, January 20, 2008 3:05 PM
  • You can't use SQL Convert function in Select method of DataTable class. Available Convert function which can be used is not from SQL but from .NET and have only two parameters. You can try something like this:

    String sql = "employee_id = " + employee_id + " AND w_date = '" + w_date + "'";


    pAYROLLDataSet.WORKINGTIMES.Locale = CultureInfo.CurrentCulture;

    PAYROLLDataSet.WORKINGTIMESRow[] workingtime_row = (PAYROLLDataSet.WORKINGTIMESRow[])pAYROLLDataSet.WORKINGTIMES.Select(sql);

     

    I'm not sure what type is employee_id but if it's string type then you will add single quotes to the sql expression. For datetime comparation, it is done as strings, so all you have to do is to adjust the string output from w_date variable. One way is to use ToString() with some format string in order to be equal to the date format of WORKINGTIMES table's Locale format, or to change the table's Locale to be the same as current culture so you will not have to use ToString().

    Sunday, January 20, 2008 3:13 PM
  •  

    Hi, Thanks for your explaintion

     

    I will tell you my problem,

     

    I want to store information of employees and the their working date, Employee(employee_id varchar(10), w_date datetime,salary decimal(8))

    But in sqlserver we just only have datetime datatype. So, I must use datetime.

    When we store the value ('emp01',convert(datetime,'25/1/2007/',103) to that table, we will get the result 'emp01'; '25/1/2007 4:2:00', the value in table quite diffrence to the origin value because of the system add current time to datetime column.

     

    These of 2 fields are primary key (employee_id,w_date), when I want to know how much money that employee recive on '25/1/2007'

     

    - The first way I did is: using the function that Visual Studio .Net 2005 auto generate

    pAYROLLDataSet.WORKINGTIMES.FindByEMPLOYEE_IDW_DATE(employee_id,'25/1/2007')

     

    this function will return null value, because the value:'25/1/2007 4:2:00' <> '25/1/2007'

    -The second way is using pAYROLLDataSet.WORKINGTIMES.Select(...

    will raise exception

    Sunday, January 20, 2008 3:46 PM
  • You can't use autogenerated FindBy'keycolumns' method because that is used for geting one row only. You must use query string something like this:

    String sql = "employee_id = " + employee_id + " AND w_date >= '" + w_date + "' AND w_date < '" + w_date.AddDays(1) + "'";

     

    This way of querying table for one day only rows is right way in SQL Server also, instead of using Convert or DateDiff.

     

    Sunday, January 20, 2008 3:57 PM