none
Data type mismatch in criteria expression RRS feed

  • Question

  • Hello All,

                I am trying to add data from a dataset to an access table(.mdb). I am getting this error when I try to insert the values in the table. This is the code

     DataSet ds = new DataSet();
    
         Console.Write("The NAME IS: " + FileName.Text.ToString());
    
         ds.ReadXml("C:\\Dallas_Test\\Offense_7_1_2010\\" + FileName.Text.ToString());
    
    
    
         ADOX.Catalog cat = new Catalog();
    
         cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Dallas_Test\\Offense_7_Test75_2010\\Offense.mdb;Jet OLEDB:Engine Type=5");
    
    
    
         //Create table in New Created Database
    
         OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\\Dallas_Test\\Offense_7_Test75_2010\\Offense.mdb");
    
    
    
         connection.Open();
    
    
    
         OleDbCommand olecom = new OleDbCommand("create table Offense([AgencyORI] Text,[AgencyName] Text,[offenseservicenumber] Text, [offensedate] Text, [offensereporteddate] Text,[offensestarttime] Text, [offensestoptime] Text, [offensetimedispatched] Text, [offensereportingarea] Number, " +
    
          " [offensebeat] Number,[offensewatch] Number, [offensesignal1] Number, [offensesignal2] Number, [offensename] Text, [offenserace] Text,[offensegender] Text, [offenseage] Number, [offenseblock] Number,[offensedirection] Number, [offensestreet] Text, [offenseapartment] Number, [offensecity] Text,[offensestate] Text, [offensezip] Number, " +
    
          " [offensebusinessblock] Text, [offensebusinessdirection] Text , [offensebusinessstreet] Text, [offensebusinesscity] Text, [offensepropertyattackcode] Text, [offensepremises] Text, [offensedateofoccurence1] Text, [offensetimeofoccurence1] Text, [offensedateofoccurence2] Text, [offensetimeofoccurence2] Text, [offenseucr1] Number, " +
    
          " [offenseucr2] Number, [offensemethodofoffense] Text, [offenseweather] Text, [offensefamilyviolence] Text, [offensegangacitivty] Text, [offensereportofficerbadge1] Number, " +
    
          " [offensereportingofficerbadge2] Number,[offenserecorddate] Text, [offensestatus] Text )", connection);
    
    
    
         olecom.ExecuteNonQuery();
    
    
    
        
    
         // Iterate through each row and Write it in table 
    
    
    
    
    
         foreach (DataRow r in ds.Tables[0].Rows)
    
         {
    
    
    
          String AgencyORI = r["AgencyORI"].ToString();
    
    
    
          String AgencyName = r["AgencyName"].ToString();
    
    
    
          String offenseservicenumber = r["offenseservicenumber"].ToString();
    
          String offensedate = Convert.ToDateTime(r["offensedate"]).ToShortTimeString();
    
    
    
          String offensereporteddate = Convert.ToDateTime(r["offensereporteddate"]).ToShortTimeString();
    
    
    
          String offensestarttime = Convert.ToDateTime(r["offensestarttime"]).ToShortTimeString();
    
          
    
          String offensestoptime = Convert.ToDateTime(r["offensestoptime"]).ToShortTimeString();
    
    
    
          String offensetimedispatched = Convert.ToDateTime(r["offensetimedispatched"]).ToShortTimeString();
    
    
    
          int offensereportingarea = Convert.ToInt32(r["offensereportingarea"]);
    
    
    
          int offensebeat = Convert.ToInt32(r["offensebeat"]);
    
          int offensewatch = Convert.ToInt32(r["offensewatch"]);
    
          String offensesignal1 = r["offensesignal1"].ToString();
    
          String offensesignal2 = r["offensesignal2"].ToString();
    
    
    
          String offenserace = r["offenserace"].ToString();
    
          String offensename = r["offensename"].ToString();
    
          String offensegender = r["offensegender"].ToString();
    
          int offenseage = 0;
    
          int offenseblock = 0;
    
          int offensedirection = 0;
    
          if (r["offenseage"].GetType()==typeof(Int32))
    
          
    
          {
    
            offenseage = Convert.ToInt32(r["offenseage"]);
    
          }
    
           
    
          else
    
          {
    
           
    
           r["offenseage"] = 0;
    
          }
    
    
    
          if (r["offenseblock"].GetType() == typeof(Int32))
    
          {
    
            offenseblock = Convert.ToInt32(r["offenseblock"]);
    
          }
    
    
    
          else
    
          {
    
    
    
           r["offenseblock"] = 0;
    
          }
    
          
    
          //int offenseblock = Convert.ToInt32(r["offenseblock"]);
    
    
    
          if (r["offensedirection"].GetType() == typeof(Int32))
    
          {
    
            offensedirection = Convert.ToInt32(r["offensedirection"]);
    
          }
    
    
    
          else
    
          {
    
    
    
           r["offensedirection"] = 0;
    
          }
    
          // int offensedirection = Convert.ToInt32(r["offensedirection"]);
    
    
    
    
    
          String offensestreet = r["offensestreet"].ToString();
    
          int offenseapartment = 0;
    
    
    
          if (r["offenseapartment"].GetType() == typeof(Int32))
    
          {
    
            offenseapartment = Convert.ToInt32(r["offenseatartment"]);
    
          }
    
    
    
          else
    
          {
    
    
    
           r["offenseapartment"] = 0;
    
          } //Convert.ToInt32(r["offenseapartment"]);
    
    
    
          String offensecity = r["offensecity"].ToString();
    
          String offensestate = r["offensestate"].ToString();
    
          int offensezip = Convert.ToInt32(r["offensezip"]);
    
    
    
          String offensebusinessblock = r["offensebusinessblock"].ToString();
    
    
    
          
    
          String offensebusinessstreet = r["offensebusinessstreet"].ToString();
    
          String offensebusinessdirection =r["offensebusinessdirection"].ToString();
    
               
    
          String offensebusinesscity = r["offensebusinesscity"].ToString();
    
          
    
          String offensepropertyattackcode = r["offensepropertyattackcode"].ToString();
    
          int offensepremises = 0;
    
          
          if (r["offensepremises"].GetType() == typeof(Int32))
    
          {
    
            offensepremises = Convert.ToInt32(r["offenspremises"]);
    
          }
    
    
    
          else
    
          {
    
    
    
           r["offensepremises"] = 0;
    
          }
    
          String offensedateofoccurence1 = Convert.ToDateTime(r["offensedateofoccurence1"]).ToString();
    
          String offensetimeofoccurence1 = Convert.ToDateTime(r["offensetimeofoccurence1"]).ToShortTimeString();
    
          String offensedateofoccurence2 = Convert.ToDateTime(r["offensedateofoccurence2"]).ToString();
    
          String offensetimeofoccurence2 = Convert.ToDateTime(r["offensetimeofoccurence2"]).ToShortTimeString();
    
    
    
          int offenseucr1 = 0;
    
          //Convert.ToInt32(r["offenseucr1"]);
    
    
    
          if (r["offenseucr1"].GetType() == typeof(Int32))
    
          {
    
            offenseucr1 = Convert.ToInt32(r["offenseucr1"]);
    
          }
    
    
    
          else
    
          {
    
    
    
           r["offenseucr1"] = 0;
    
          }
    
          int offenseucr2 = 0;
    
          
    
    
    
          if (r["offenseucr2"].GetType() == typeof(Int32))
    
          {
    
           offenseucr2 = Convert.ToInt32(r["offenseucr2"]);
    
          }
    
    
    
          else
    
          {
    
    
    
           r["offenseucr2"] = 0;
    
          }
    
    
    
          String offensemethodofoffense = r["offensepropertyattackcode"].ToString();
    
          String offenseweather = r["offenseweather"].ToString();
    
          String offensefamilyviolence = r["offensefamilyviolence"].ToString();
    
          String offensegangacitivty =r["offensegangacitivty"].ToString();
    
    
    
          int offensereportofficerbadge1 = 0;
    
          
    
    
    
          if (r["offensereportofficerbadge1"].GetType() == typeof(Int32))
    
          {
    
           offensereportofficerbadge1 = Convert.ToInt32(r["offensereportofficerbadge1"]);
    
          }
    
    
    
          else
    
          {
    
    
    
           r["offensereportofficerbadge1"] = 0;
    
          }
    
          
    
          int offensereportingofficerbadge2 = 0;
    
          if (r["offensereportingofficerbadge2"].GetType() == typeof(Int32))
    
          {
    
           offensereportingofficerbadge2 = Convert.ToInt32(r["offensereportingofficerbadge2"]);
    
          }
    
    
    
          else
    
          {
    
    
    
           r["offensereportingofficerbadge2"] = 0;
    
          }
    
          
    
          String offenserecorddate = r["offenserecorddate"].ToString();
    
          String offensestatus = r["offensestatus"].ToString();
    
    
    
          olecom = new OleDbCommand("insert into Offense([AgencyORI],[AgencyName],[offenseservicenumber],[offensedate], [offensereporteddate], [offensestarttime], [offensestoptime],[offensetimedispatched],[offensereportingarea],[offensebeat],[offensewatch], [offensesignal1], [offensesignal2], [offensename], [offenserace], [offensegender]," +
    
           " [offenseage], [offenseblock], [offensedirection], [offensestreet], [offenseapartment], [offensecity], [offensestate],[offensezip], [offensebusinessblock], [offensebusinessdirection], [offensebusinessstreet], [offensebusinesscity], [offensepropertyattackcode],[offensepremises],[offensedateofoccurence1],[offensetimeofoccurence1], [offensedateofoccurence2],[offensetimeofoccurence2],[offenseucr1],[offenseucr2],[offensemethodofoffense],[offenseweather],[offensefamilyviolence], [offensegangacitivty], [offensereportofficerbadge1], [offensereportingofficerbadge2],[offenserecorddate],[offensestatus] ) values(@AgencyORI,@AgencyName,@offenseservicenumber,@offensedate,@offensereporteddate," + 
    
           " @offensestarttime, @offensestoptime,@offensetimedispatched, @offensereportingarea, @offesebeat, @offensewatch, @offensesignal1, @offensesignal2, @offensename, @offenserace, @offensegender, @offenseage, @offenseblock, @offensedirection, @offensestreet, @offenseapartment, @offensecity, offensestate, @offensezip," +
    
           " @offensebusinessblock, @offensebusinessdirection, @offensebusinessstreet, @offensebusinesscity,@offensepropertyattackcode,@offensepremises,@offensedateofoccurence1,@offensetimeofoccurence1, @offensedateofoccurence2, @offensetimeofoccurence2 ,@offenseucr1 ,@offenseucr2, @offensemethodofoffense, @offenseweather, @offensefamilyviolence, @offensegangacitivty, @offensereportofficerbadge1, @offensereportingofficerbadge2,@offenserecorddate, @offensestatus )", connection);
    
    
    
          
    
    
    
          olecom.Parameters.AddWithValue("@AgencyORI", AgencyORI);
    
    
    
          olecom.Parameters.AddWithValue("@AgencyName", AgencyName);
    
          olecom.Parameters.AddWithValue("@offenseservicenumber", offenseservicenumber);
    
          olecom.Parameters.AddWithValue("@offensedate", offensedate);
    
          olecom.Parameters.AddWithValue("@offensereporteddate", offensereporteddate);
    
          olecom.Parameters.AddWithValue("@offensestarttime", offensestarttime);
    
          olecom.Parameters.AddWithValue("@offensestoptime", offensestoptime);
    
          olecom.Parameters.AddWithValue("@offensetimedispatched", offensetimedispatched);
    
          olecom.Parameters.AddWithValue("@offensereportingarea", offensereportingarea);
    
          olecom.Parameters.AddWithValue("@offensebeat", offensebeat);
    
          olecom.Parameters.AddWithValue("@offensewatch", offensewatch);
    
          olecom.Parameters.AddWithValue("@offensesignal1", offensesignal1);
    
          olecom.Parameters.AddWithValue("@offensesignal2", offensesignal2);
    
          olecom.Parameters.AddWithValue("@offensename", offensename);
    
          olecom.Parameters.AddWithValue("@offenserace", offenserace);
    
          olecom.Parameters.AddWithValue("@offensegender", offensegender);
    
          olecom.Parameters.AddWithValue("@offenseblock", offenseblock);
    
          olecom.Parameters.AddWithValue("@offensedirection", offensedirection);
    
          olecom.Parameters.AddWithValue("@offensestreet", offensestreet);
    
          olecom.Parameters.AddWithValue("@offenseapartment", offenseapartment);
    
          olecom.Parameters.AddWithValue("@offensecity", offensecity);
    
          olecom.Parameters.AddWithValue("@offensestate", offensestate);
    
          olecom.Parameters.AddWithValue("@offenseage", offenseage);
    
          olecom.Parameters.AddWithValue("@offenesezip", offensezip);
    
          olecom.Parameters.AddWithValue("@offensebusinessblock", offensebusinessblock);
    
          olecom.Parameters.AddWithValue("@offensebusinessdirection", offensebusinessdirection);
    
          olecom.Parameters.AddWithValue("@offensebusinessstreet", offensebusinessstreet);
    
          olecom.Parameters.AddWithValue("@offensebusinesscity", offensebusinesscity);
    
          olecom.Parameters.AddWithValue("@offensepropertyattackcode", offensepropertyattackcode);
    
          olecom.Parameters.AddWithValue("@offensepremises", offensepremises);
    
          olecom.Parameters.AddWithValue("@offensedateofoccurence1", offensedateofoccurence1);
    
          olecom.Parameters.AddWithValue("@offensetimeofoccurence1", offensetimeofoccurence1);
    
          olecom.Parameters.AddWithValue("@offensedateofoccurence2", offensedateofoccurence2);
    
          olecom.Parameters.AddWithValue("@offensetimeofoccurence2", offensetimeofoccurence2);
    
          olecom.Parameters.AddWithValue("@offenseucr1", offenseucr1);
    
          olecom.Parameters.AddWithValue("@offenseucr2", offenseucr2);
    
          olecom.Parameters.AddWithValue("@offensemethodofoffense", offensemethodofoffense);
    
          olecom.Parameters.AddWithValue("@offenseweather", offenseweather);
    
          olecom.Parameters.AddWithValue("@offensefamilyviolence", offensefamilyviolence);
    
          olecom.Parameters.AddWithValue("@offensegangacitivty", offensegangacitivty);
    
          olecom.Parameters.AddWithValue("@offensereportofficerbadge1", offensereportofficerbadge1);
    
          olecom.Parameters.AddWithValue("@offensereportingofficerbadge2", offensereportingofficerbadge2);
    
          olecom.Parameters.AddWithValue("@offenserecorddate", offenserecorddate);
    
          olecom.Parameters.AddWithValue("@offensestatus", offensestatus);
    
    
    
          olecom.ExecuteNonQuery();
    
    
    
         }
    
        
    
          //Close the Connection 
    
    
    
    
    
         connection.Close();     
    
        

     

    I am getting this error on olecom.ExecuteNon Query() method. I have also tried to convert Date fields to String but still I haven't  helped. I am also not prompted to field which is causnig this issue.

     

    Can someone help me with this?

     

    Thanks,

    Sid

    • Edited by Sidhanshu Thursday, July 29, 2010 10:47 PM delete commented code
    • Moved by Figo Fei Friday, July 30, 2010 5:24 AM (From:Visual C# General)
    Thursday, July 29, 2010 10:44 PM

Answers

  • Hi Sidhanshu,

    This error says that you have two data types that are not compatible. After checking your source code, I found that when you create table "Offense", you declared “[offensesignal1] Number, [offensesignal2] Number” in the command text, which means that "offensesignal1" and "offensesignal2" column’s data type is Number, however, you assigned string values to this two columns when you insert new row to the table.

    String offensesignal1 = r["offensesignal1"].ToString();
    String offensesignal2 = r["offensesignal2"].ToString();

    I suggest you carefully check the column data type in the table and the values’ data type which you want to insert into the column, making sure that they are compatible with each other.

    If you have other questions, please feel free to let me know.

    Best regards,
    Alex Liang
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Sidhanshu Friday, July 30, 2010 11:31 PM
    Friday, July 30, 2010 12:22 PM
    Moderator

All replies

  • I moved it to ADO.NET forum for better answer.

    Thanks for understanding.


    Figo Fei
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, July 30, 2010 5:24 AM
  • Hi Sidhanshu,

    This error says that you have two data types that are not compatible. After checking your source code, I found that when you create table "Offense", you declared “[offensesignal1] Number, [offensesignal2] Number” in the command text, which means that "offensesignal1" and "offensesignal2" column’s data type is Number, however, you assigned string values to this two columns when you insert new row to the table.

    String offensesignal1 = r["offensesignal1"].ToString();
    String offensesignal2 = r["offensesignal2"].ToString();

    I suggest you carefully check the column data type in the table and the values’ data type which you want to insert into the column, making sure that they are compatible with each other.

    If you have other questions, please feel free to let me know.

    Best regards,
    Alex Liang
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Sidhanshu Friday, July 30, 2010 11:31 PM
    Friday, July 30, 2010 12:22 PM
    Moderator
  • Hi Alex,

         Thanks for the suggestion. I changed the data type of fields offensesignal1 and offensesignal2 to String. But that also did not remove the issue.

     

    Please let me know if you see any other issue.

     

    Thanks,

    Sid

    Friday, July 30, 2010 3:57 PM
  • Thanks for the suggestion, I had a mistake in my datatype mapping. On correction the error got resolved.

    Thanks,

    Sid

    Friday, July 30, 2010 11:31 PM