Peter Chan
asked on
Data type mismatch
Hi,
With these codes, against Access DB
I do get this exception. why?
Data type mismatch in criteria expression.Microsoft Access Database Engine
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();
I do get this exception. why?
Data type mismatch in criteria expression.Microsoft Access Database Engine
Why do you use OleDbType.VarWChar for "@obj3" and OleDbType.VarChar for others?
Can you post here the "ite_tab" table structure
ASKER
As this column can have unicode inside.
ASKER
I've changed a little bit in these 2 lines (but I still get the same exception after the change)
these 4 columns are Date/Time
ite_dt,fld5,chg_date,upd_d ate
all other columns are Short Text.
cmd.Parameters.Add("@fld11", OleDbType.Integer).Value = Convert.ToInt32(tb_fld11.Text);
cmd.Parameters.Add("@fld12", OleDbType.Integer).Value = Convert.ToInt32(tb_fld12.Text);
only these 2 columns, those column names like ".._id" are numbers, these 4 columns are Date/Time
ite_dt,fld5,chg_date,upd_d
all other columns are Short Text.
You are trying to insert datediff('yyyy',@ite_dt2,D ate()) 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?
You need to insert a date value. What was your intention when you have written this datediff statement?
ASKER
Sorry, actually
fld5
is also one Date/Time column. thanks
fld5
is also one Date/Time column. thanks
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,D ate()) function. Just tell me what are you intentions, and I can help you out
ASKER
Just the difference of 2 dates, which is a number, and also the column data type is number as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.