Solved

Data type mismatch

Posted on 2013-11-26
9
521 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
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

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

Expert Comment

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

Author Comment

by:HuaMinChen
ID: 39679610
As this column can have unicode inside.
0
 
LVL 10

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 24

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 10

Author Comment

by:HuaMinChen
ID: 39679743
Sorry, actually

fld5

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

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 10

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 24

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now