Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Data type mismatch

Posted on 2013-11-26
9
Medium Priority
?
549 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 435 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

604 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