Data type mismatch

Posted on 2013-11-26
Last Modified: 2013-11-26
With these codes, against Access DB
                    cmd = new OleDbCommand("insert into ite_tab (ite_abbr,ite_name,obj2_id,obj2,obj3_id,obj3,ent_id,obj4cat,typ2_id,obj4group,typ3_id,obj4title,obj5_typ4_fullname,obj5_typ4_name,obj5_typ4_ext,ite_fld10,fld11,fld12,fld13,fld14,fld15,ite_dt,fld5,chg_date,fld6,upd_date) values (@par_id,@name,@obj2_id,@obj2,@obj3_id,@obj3,@ent,@obj4cat,@ent_grp_id,@obj4group,@typ3_id,@obj4title,@fl_fullname,@fl_name,@fl_ext,@pass,@fld11,@fld12,iif(isnull(@fld13),'',@fld13),iif(isnull(@fld14),'',@fld14),iif(isnull(@fld15),'',@fld15),@ite_dt,datediff('yyyy',@ite_dt2,Date()),@chg_dt,@fld6,Date())", conn);
                    if (tb_iteabbr != null)
                        id2 = tb_iteabbr.Text.Trim();

                    name = tb_itename.Text.Trim();
                    fl_fullname = tb_ph_typ4_fullname.Text.Trim();
                    fl_name = tb_ph_typ4_name.Text.Trim();
                    cmd.Parameters.Add("@par_id", OleDbType.VarChar).Value = id2;
                    cmd.Parameters.Add("@name", OleDbType.VarChar).Value = name;
                    cmd.Parameters.Add("@obj2_id", OleDbType.VarChar).Value = obj2_ddl.SelectedValue.ToString();
                    cmd.Parameters.Add("@obj2", OleDbType.VarWChar).Value = obj2_ddl.SelectedItem.ToString();
                    cmd.Parameters.Add("@obj3_id", OleDbType.VarChar).Value = obj3_ddl.SelectedValue.ToString();
                    cmd.Parameters.Add("@obj3", OleDbType.VarWChar).Value = obj3_ddl.SelectedItem.ToString();
                    cmd.Parameters.Add("@ent", OleDbType.VarChar).Value = ent_cat_ddl.SelectedValue.ToString();
                    cmd.Parameters.Add("@obj4cat", OleDbType.VarChar).Value = ent_cat_ddl.SelectedItem.ToString();
                    cmd.Parameters.Add("@ent_grp_id", OleDbType.VarChar).Value = typ2_ddl.SelectedValue.ToString();
                    cmd.Parameters.Add("@obj4group", OleDbType.VarChar).Value = typ2_ddl.SelectedItem.ToString();
                    cmd.Parameters.Add("@typ3_id", OleDbType.VarChar).Value = typ3_ddl.SelectedValue.ToString();
                    cmd.Parameters.Add("@obj4title", OleDbType.VarChar).Value = typ3_ddl.SelectedItem.ToString();
                    cmd.Parameters.Add("@pass", OleDbType.VarChar).Value = new_pass;
                    cmd.Parameters.Add("@fl_fullname", OleDbType.VarChar).Value = fl_fullname;
                    cmd.Parameters.Add("@fl_name", OleDbType.VarChar).Value = fl_name;
                    cmd.Parameters.Add("@fl_ext", OleDbType.VarChar).Value = tb_flext.Text;
                    cmd.Parameters.Add("@chg_dt", OleDbType.DBDate).Value = Convert.ToDateTime(tb_chg_dt.Text.Trim());
                    cmd.Parameters.Add("@fld11", OleDbType.Decimal).Value = Convert.ToDecimal(tb_fld11.Text);
                    cmd.Parameters.Add("@fld12", OleDbType.Decimal).Value = Convert.ToDecimal(tb_fld12.Text);
                    cmd.Parameters.Add("@fld13", OleDbType.VarChar).Value = tb_fld13.Text;
                    cmd.Parameters.Add("@fld14", OleDbType.VarChar).Value = tb_fld14.Text;
                    cmd.Parameters.Add("@fld15", OleDbType.VarChar).Value = tb_fld15.Text;
                    cmd.Parameters.Add("@ite_dt", OleDbType.DBDate).Value = DateTime.ParseExact(tb_ite_dt.Text.Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                    cmd.Parameters.Add("@ite_dt2", OleDbType.DBDate).Value = DateTime.ParseExact(tb_ite_dt.Text.Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                    cmd.Parameters.Add("@fld6", OleDbType.VarChar).Value = rbl_fld6.SelectedItem.Text;

Open in new window

I do get this exception. why?
Data type mismatch in criteria expression.Microsoft Access Database Engine
Question by:HuaMinChen
  • 5
  • 4
LVL 24

Expert Comment

ID: 39679605
Why do you use OleDbType.VarWChar for "@obj3" and OleDbType.VarChar for others?
LVL 24

Expert Comment

ID: 39679608
Can you post here the "ite_tab" table structure
LVL 10

Author Comment

ID: 39679610
As this column can have unicode inside.
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

LVL 10

Author Comment

ID: 39679639
I've changed a little bit in these 2 lines (but I still get the same exception after the change)

                    cmd.Parameters.Add("@fld11", OleDbType.Integer).Value = Convert.ToInt32(tb_fld11.Text);
                    cmd.Parameters.Add("@fld12", OleDbType.Integer).Value = Convert.ToInt32(tb_fld12.Text);

Open in new window

only these 2 columns, those column names like ".._id" are numbers,

these 4 columns are Date/Time

all other columns are Short Text.
LVL 24

Expert Comment

ID: 39679737
You are trying to insert datediff('yyyy',@ite_dt2,Date()) into a date/time column. Datediff returns integer.
You need to insert a date value. What was your intention when you have written this datediff statement?
LVL 10

Author Comment

ID: 39679743
Sorry, actually


is also one Date/Time column. thanks
LVL 24

Expert Comment

ID: 39679777
That is why I have asked. What are you trying to have inserted into this column? You will not be able to insert a result of datediff('yyyy',@ite_dt2,Date()) function. Just tell me what are you intentions, and I can help you out
LVL 10

Author Comment

ID: 39679785
Just the difference of 2 dates, which is a number, and also the column data type is number as well.
LVL 24

Accepted Solution

chaau earned 145 total points
ID: 39679787
Can you change temporarily obj2 to Char and see if it works:
                    cmd.Parameters.Add("@obj2", OleDbType.VarChar).Value = obj2_ddl.SelectedItem.ToString();

Open in new window


Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question