locked
error The data types ntext and varchar are incompatible in the equal to operator

    Question

  • Hello,i'm have problem with my project,i have this code:

    string day = DateTime.Now.Date.ToString("dddd", System.Globalization.CultureInfo.CreateSpecificCulture("id-ID"));
            int i;
            DataTable dt = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter("select * from t_Day where dAy='" + day + "'", clsConn.objConn);
                da.Fill(dt);
                for (i = 0; i < dt.Rows.Count; i++)
                {
                    txt_checkIn.Text = dt.Rows[i]["check_in"].ToString();
                    txt_check_out.Text = dt.Rows[i]["check_out"].ToString();
                       
                }
    when i debug this project,then show error
    "The data types ntext and varchar are incompatible in the equal to operator"
    in database,type dAy,check_in,check_out=ntext,

    please give me for solution"thank's


    Monday, May 11, 2009 1:31 AM

Answers

  • Your error comes from the database because in SQL, you cannot compare an ntext field with a varchar field using the '=' operator.

    You cannot even compare an ntext to an ntext with an '='.   SQL will allow you to compare a converted ntext to a varchar.   Use the following line of code

    SqlDataAdapter da = new SqlDataAdapter("select * from t_Day where convert(varchar,dAy)='" + day + "'", clsConn.objConn);


    Les Potter, Xalnix Corporation, Yet Another C# Blog
    • Marked as answer by Bin-ze Zhao Wednesday, May 13, 2009 6:36 AM
    Tuesday, May 12, 2009 12:11 PM

All replies

  • What you are doing is:

    1) Converting a DateTime value (which is a machine representation of time) into a specific culture string.
    2) Trying to query a database using that culture specific string.
    3) Failing to use the SqlCommand object to build your SQL command string.

    Both your computer and the database are capable of working with time as a machine representation of time. There is no need (and in fact it's counter-productive) to first convert the value into a string (culture specific or not).

    SqlCommand command = new SqlCommand("select * from t_Day where dAy=@day");
    command.Parameters.Add("@day", DateTime.Now);
    command.Connection = clsConn.objConn; // I don't know where you're getting your connection, that looks suspect
    SqlDataAdapter da = new SqlDataAdapter(command);
    da.Fill(dt);
    
    • Edited by Tergiver Monday, May 11, 2009 2:58 AM
    Monday, May 11, 2009 2:55 AM
  • After 3 edits with the crappy forum editor, I think I got it to look right.
    Monday, May 11, 2009 2:58 AM
  • doing me is, i have a table,on the table there 3 fields:Hari,Jam_Keluar,Jam_Masuk. This my table:

    and i want fields Jam_Masuk and Jam_Keluar display on the textbox1&textbox2 accordingly today
    ex:if today is monday i want Jam_Masuk,&Jam_Keluar display on textbox 1&2 same with Monday on the table, i use code like that,but error,plase give me solution, i hope copartners' understand with my problem, thank's
    this my form


    Hello,i'm have problem with my project,i have this code: string day = DateTime.Now.Date.ToString("dddd", System.Globalization.CultureInfo.CreateSpecificCulture("id-ID")); int i; DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from t_Day where dAy='" + day + "'", clsConn.objConn); da.Fill(dt); for (i = 0; i < dt.Rows.Count; i++) { txt_checkIn.Text = dt.Rows[i]["check_in"].ToString(); txt_check_out.Text = dt.Rows[i]["check_out"].ToString(); } when i debug this project,then show error "The data types ntext and varchar are incompatible in the equal to operator" in database type dAy,check_in,check_out=ntext, please give me for solution"thank's
    Tuesday, May 12, 2009 10:49 AM
  • Your error comes from the database because in SQL, you cannot compare an ntext field with a varchar field using the '=' operator.

    You cannot even compare an ntext to an ntext with an '='.   SQL will allow you to compare a converted ntext to a varchar.   Use the following line of code

    SqlDataAdapter da = new SqlDataAdapter("select * from t_Day where convert(varchar,dAy)='" + day + "'", clsConn.objConn);


    Les Potter, Xalnix Corporation, Yet Another C# Blog
    • Marked as answer by Bin-ze Zhao Wednesday, May 13, 2009 6:36 AM
    Tuesday, May 12, 2009 12:11 PM
  • ok' i now see about my problem,thank's for all, i'll be comeback on another questions


    Tuesday, May 12, 2009 12:40 PM