Solved

Data type mismatch

Posted on 2013-11-26
9
543 Views
Last Modified: 2013-11-26
Hi,
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;
                    cmd.ExecuteNonQuery();

Open in new window


I do get this exception. why?
Data type mismatch in criteria expression.Microsoft Access Database Engine
0
Comment
Question by:HuaMinChen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39679605
Why do you use OleDbType.VarWChar for "@obj3" and OleDbType.VarChar for others?
0
 
LVL 25

Expert Comment

by:chaau
ID: 39679608
Can you post here the "ite_tab" table structure
0
 
LVL 11

Author Comment

by:HuaMinChen
ID: 39679610
As this column can have unicode inside.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 11

Author Comment

by:HuaMinChen
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
ite_dt,fld5,chg_date,upd_date

all other columns are Short Text.
0
 
LVL 25

Expert Comment

by:chaau
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?
0
 
LVL 11

Author Comment

by:HuaMinChen
ID: 39679743
Sorry, actually

fld5

is also one Date/Time column. thanks
0
 
LVL 25

Expert Comment

by:chaau
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
0
 
LVL 11

Author Comment

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

Accepted Solution

by:
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

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

690 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