Databases
StackOverflow2013
Postsdbo
POData type mismatch in criteria expression
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POData type mismatch in criteria expression
    primarykey
    data
    text
    <p>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</p> <pre><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; } if (r["offensedirection"].GetType() == typeof(Int32)) { offensedirection = Convert.ToInt32(r["offensedirection"]); } else { r["offensedirection"] = 0; } String offensestreet = r["offensestreet"].ToString(); int offenseapartment = 0; if (r["offenseapartment"].GetType() == typeof(Int32)) { offenseapartment = Convert.ToInt32(r["offenseatartment"]); } else { r["offenseapartment"] = 0; } 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; 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(); } connection.Close(); </code></pre> <p>I am getting an error on olecom.ExecuteNon Query() method. I have also tried to convert Date fields to String but it still didn't help. I am also not told to field which is causing this issue. Can someone help me with this? Thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USEric Darchis
    1. USSid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POData type mismatch in criteria expression
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COIt'd be helpful if you formatted your code, and only posted the relevant sections...
      singulars
      1. POData type mismatch in criteria expression
      1. USReed Copsey
    2. COSorry for such long code, the reason i have posted long code is that I may have made a mistake in code(a typo or String/int interchange). I think that if I trim this code, I might hide a possible problem area. Thanks for the comment though. If you still want me to trim the code, please let me know. Thanks, Sid
      singulars
      1. POData type mismatch in criteria expression
      1. USSid
    3. CO@sidhanshu: At least get rid of the extra spaces between the lines so we can read it.
      singulars
      1. POData type mismatch in criteria expression
      1. USCarter Medlin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload