0

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 peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

2
Contributors
1
Reply
4
Views
7 Years
Discussion Span
Last Post by nick.crane
1

In case you have not yet figured this out.
in your query
@offensebeat is entered as "@offesebeat"
and
@offensestate is entered as "offensestate"

Next time please remember to use [code]

[/code] tags around your code to make it easier to read.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.