About ADO.Net in MVC

Question

hello, hope everyone is fine I am saving some data to sql database using ado.net Following is the code, but getting error of - ' Implicit conversion from data type datetime to bit is not allowed. Use the CONVERT function to run this query ' string query = "INSERT INTO drug_details VALUES(@image,@name, @label, @erx_required,@quantity, @price, @dosage,@ndc, @tier_one, @tier_two, @tier_three, @tier_four, @tier_five,@drug_form_id, @drug_category_id, @drug_class_id,@drug_type_id, @age_group_id, @min_quantity,@max_quantity, @refillable, @refill_limit,@created_at, @updated_at,@description,@contradiction,@administration,@missed_dose,@interaction,@monitoring,@side_effects,@storage,@counselling,@brand,@active,@qty_allowed) " ; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue( "@image" , filename); cmd.Parameters.AddWithValue( "@name" ,txtname); cmd.Parameters.AddWithValue( "@label" , txtlabelname); cmd.Parameters.AddWithValue( "@erx_required" , true ); cmd.Parameters.AddWithValue( "@quantity" , txtquantity); cmd.Parameters.AddWithValue( "@price" , txtprice); cmd.Parameters.AddWithValue( "@dosage" , txtdosage); cmd.Parameters.AddWithValue( "@ndc" , txtndc); cmd.Parameters.AddWithValue( "@tier_one" , txttierone); cmd.Parameters.AddWithValue( "@tier_two" , txttiertwo); cmd.Parameters.AddWithValue( "@tier_three" , txttierthree); cmd.Parameters.AddWithValue( "@tier_four" , txttierfour); cmd.Parameters.AddWithValue( "@tier_five" , txttierfive); cmd.Parameters.AddWithValue( "@drug_form_id" , ddldrugform); cmd.Parameters.AddWithValue( "@drug_category_id" , ddldrugcategory); cmd.Parameters.AddWithValue( "@drug_class_id" , ddldrugclass); cmd.Parameters.AddWithValue( "@drug_type_id" , ddldrugtype); cmd.Parameters.AddWithValue( "@age_group_id" , ddlagegroup); cmd.Parameters.AddWithValue( "@min_quantity" , txtminquantity); cmd.Parameters.AddWithValue( "@max_quantity" , txtmaxquantity); cmd.Parameters.AddWithValue( "@refillable" , true ); cmd.Parameters.AddWithValue( "@refill_limit" , txtrefills); cmd.Parameters.AddWithValue( "@created_at" , Convert .ToDateTime( DateTime.Now)); cmd.Parameters.AddWithValue( "@updated_at" , Convert .ToDateTime(DateTime.Now)); cmd.Parameters.AddWithValue( "@description" , string.Empty); cmd.Parameters.AddWithValue( "@contradiction" , string.Empty); cmd.Parameters.AddWithValue( "@administration" , string.Empty); cmd.Parameters.AddWithValue( "@missed_dose" , string.Empty); cmd.Parameters.AddWithValue( "@interaction" , string.Empty); cmd.Parameters.AddWithValue( "@monitoring" , string.Empty); cmd.Parameters.AddWithValue( "@side_effects" , string.Empty); cmd.Parameters.AddWithValue( "@storage" , string.Empty); cmd.Parameters.AddWithValue( "@counselling" , string.Empty); cmd.Parameters.AddWithValue( "@brand" , true ); cmd.Parameters.AddWithValue( "@active" , true ); cmd.Parameters.AddWithValue( "@qty_allowed" , true ); con. Open (); cmd.ExecuteScalar(); con. Close (); SO what am I doing wrong here ? Here , created_at & updated_at columns is of datatype Datetime in Sql table

Rijwan Ansari · Answer

Check column order and datatype accordingly in query and sql table. Regards Rijwan

Sachin Singh · Answer

you have so many parameters , open your table in visual studio and copy paste each column with @ symbol , so that ordering will be proper. you are inserting datetime in bit data type , also make sure that in database your datatype is datetime.

Guest User · Answer

Hello everyone thank you for your helpp The issue has been fixed and it was order issue . Now with same parameters and same column order, I am updating the data, but it is keeps on loading and nothing happens. Following is the Update code. Please let me know what I'm missing here. string query = "UPDATE drug_details SET name=@name,label=@label, price=@price, quantity=@quantity,image=@image, description=@description, counselling=@counselling,ndc=@ndc, dosage=@dosage, brand=@brand, drug_form_id=@drug_form_id, drug_category_id=@drug_category_id, drug_class_id=@drug_class_id,drug_type_id=@drug_type_id, age_group_id=@age_group_id, contradiction=@contradiction,administration=@administration, missed_dose=@missed_dose, interaction=@interaction,monitoring=@monitoring, side_effects=@side_effects, storage=@storage,refillable=@refillable, refill_limit=@refill_limit,tier_one=@tier_one,tier_two=@tier_two,tier_three=@tier_three,tier_four=@tier_four,tier_five=@tier_five,erx_required=@erx_required,active=@active,created_at=@created_at,updated_at=@updated_at,min_quantity=@min_quantity,max_quantity=@max_quantity,qty_allowed=@qty_allowed where id=@id"; string constr = ConfigurationManager.ConnectionStrings["cs"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Parameters.AddWithValue("@id",Convert.ToInt32(Session["DrugIdAtEdit"].ToString())); cmd.Parameters.AddWithValue("@name", txtname); cmd.Parameters.AddWithValue("@label", txtlabelname); cmd.Parameters.AddWithValue("@price", txtprice); cmd.Parameters.AddWithValue("@quantity", txtquantity); cmd.Parameters.AddWithValue("@image", filename); cmd.Parameters.AddWithValue("@description", s.description); cmd.Parameters.AddWithValue("@counselling", s.counselling); cmd.Parameters.AddWithValue("@ndc", txtndc); cmd.Parameters.AddWithValue("@dosage", txtdosage); cmd.Parameters.AddWithValue("@brand", Convert.ToBoolean(1)); cmd.Parameters.AddWithValue("@drug_form_id", ddldrugform); cmd.Parameters.AddWithValue("@drug_category_id", ddldrugcategory); cmd.Parameters.AddWithValue("@drug_class_id", ddldrugclass); cmd.Parameters.AddWithValue("@drug_type_id", ddldrugtype); cmd.Parameters.AddWithValue("@age_group_id", ddlagegroup); cmd.Parameters.AddWithValue("@contradiction", s.contradiction); cmd.Parameters.AddWithValue("@administration", s.administration); cmd.Parameters.AddWithValue("@missed_dose", s.missed_dose); cmd.Parameters.AddWithValue("@interaction", s.interaction); cmd.Parameters.AddWithValue("@monitoring", s.monitoring); cmd.Parameters.AddWithValue("@side_effects", s.side_effects); cmd.Parameters.AddWithValue("@storage", s.storage); cmd.Parameters.AddWithValue("@refillable", ddlrefillallowed); cmd.Parameters.AddWithValue("@refill_limit", txtrefills); cmd.Parameters.AddWithValue("@tier_one", txttierone); cmd.Parameters.AddWithValue("@tier_two", txttiertwo); cmd.Parameters.AddWithValue("@tier_three", txttierthree); cmd.Parameters.AddWithValue("@tier_four", txttierfour); cmd.Parameters.AddWithValue("@tier_five", txttierfive); cmd.Parameters.AddWithValue("@erx_required", ddlerxrequired); cmd.Parameters.AddWithValue("@active", Convert.ToBoolean(1)); cmd.Parameters.AddWithValue("@created_at", s.created_at); cmd.Parameters.AddWithValue("@updated_at", DateTime.Now); cmd.Parameters.AddWithValue("@min_quantity", txtminquantity); cmd.Parameters.AddWithValue("@max_quantity", txtmaxquantity); cmd.Parameters.AddWithValue("@qty_allowed", Convert.ToBoolean(1)); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } }

Jignesh Kumar · Answer

Hi, Please check you insert statement column order is proper. You are passing datatime values in bit type column of table. Always use Insert into TableName ( image, name, label , erx_required ) values( @image,@name, @label, @erx_required); proper columname and respective values in insert statement.