c# - Update and Delete from DataGridView not functioning -
i have datagridview displays 3 rows, when try update records says changed records. when check database records not updated.
and in delete button says must select record delete selected row in datagridview
using system; using system.collections.generic; using system.componentmodel; using system.data; using system.drawing; using system.linq; using system.text; using system.windows.forms; using system.data.sql; using system.data.sqlclient; namespace rfg_inventory_and_management_system { public partial class adminform : form { sqlcommand cmd; sqlconnection con = new sqlconnection(@"data source=.\sqlexpress;attachdbfilename=c:\users\acer\documents\visual studio 2010\projects\rfg inventory , management system\rfg inventory , management system\database1.mdf;integrated security=true;user instance=true"); sqlcommandbuilder cmdbl; sqldataadapter adap; dataset ds; int id = 0; public adminform() { initializecomponent(); payratesdisplay(); nonmemberdisplay(); } //display data in datagridview private void payratesdisplay() { con.open(); datatable dt = new datatable(); adap = new sqldataadapter("select * tbl_payrates", con); adap.fill(dt); datagridview4.datasource = dt; con.close(); } private void nonmemberdisplay() { con.open(); datatable dt = new datatable(); adap = new sqldataadapter("select tbl_nonmember.*, tbl_customer.lname, tbl_customer.fname, tbl_customer.mname, tbl_customer.gender, tbl_customer.age, tbl_customer.membership_type tbl_nonmember inner join tbl_customer on tbl_nonmember.customerid = tbl_customer.customerid", con); adap.fill(dt); datagridview2.datasource = dt; con.close(); } //clear data private void cleardata() { tbmemship.text = ""; tbperses.text = ""; tbdisc.text = ""; id = 0; } private void adminform_load(object sender, eventargs e) { // todo: line of code loads data 'database1dataset.tbl_customer' table. can move, or remove it, needed. this.tbl_customertableadapter.fill(this.database1dataset.tbl_customer); // todo: line of code loads data 'database1dataset.tbl_nonmember' table. can move, or remove it, needed. this.tbl_nonmembertableadapter.fill(this.database1dataset.tbl_nonmember); } private void fillbytoolstripbutton_click(object sender, eventargs e) { try { this.tbl_customertableadapter.fillby(this.database1dataset.tbl_customer); } catch (system.exception ex) { system.windows.forms.messagebox.show(ex.message); } } //----------------------------------buttons pay rate-------------------------------------------------------------// //datagridview1 rowheadermouseclick event private void datagridview4_rowheadermouseclick(object sender, datagridviewcellmouseeventargs e) { tbmemship.text = datagridview4.rows[e.rowindex].cells[0].value.tostring(); tbperses.text = datagridview4.rows[e.rowindex].cells[1].value.tostring(); tbdisc.text = datagridview4.rows[e.rowindex].cells[2].value.tostring(); } private void btn_update_click(object sender, eventargs e) { if (tbmemship.text != "" && tbperses.text != "" && tbdisc.text != "") { cmd = new sqlcommand("update tbl_payrates set membership=@membership,persession=@persession discounted=@discounted", con); con.open(); cmd.parameters.addwithvalue("@membership", tbmemship.text); cmd.parameters.addwithvalue("@persession", tbperses.text); cmd.parameters.addwithvalue("@discounted", tbdisc.text); cmd.executenonquery(); messagebox.show("record updated successfully"); con.close(); payratesdisplay(); cleardata(); } else { messagebox.show("please select record update"); } } private void btn_pradd_click(object sender, eventargs e) { dialogresult dr = messagebox.show("are sure save changes", "message", messageboxbuttons.yesno, messageboxicon.information); if (dr == dialogresult.yes) { con = new sqlconnection(@"data source=.\sqlexpress;attachdbfilename=c:\users\acer\documents\visual studio 2010\projects\rfg inventory , management system\rfg inventory , management system\database1.mdf;integrated security=true;user instance=true"); con.open(); cmd = new sqlcommand("insert tbl_payrates (membership, persession, discounted) values (@membership, @persession, @discounted)", con); cmd.parameters.addwithvalue("@membership", tbmemship.text); cmd.parameters.addwithvalue("@persession", tbperses.text); cmd.parameters.addwithvalue("@discounted", tbdisc.text); cmd.executenonquery(); } } private void btn_prdel_click_1(object sender, eventargs e) { //update button update dataset after insertion,upadtion or deletion dialogresult dr = messagebox.show("are sure save changes", "message", messageboxbuttons.yesno, messageboxicon.information); if (dr == dialogresult.yes) { if (id != 0) { cmd = new sqlcommand("delete tbl_payrates id=@id", con); con.open(); cmd.parameters.addwithvalue("@id", id); cmd.executenonquery(); con.close(); messagebox.show("record deleted successfully!"); payratesdisplay(); cleardata(); } else { messagebox.show("please select record delete"); } } } } }
the whole attachdbfilename= approach flawed - @ best! when running app in visual studio, copying around .mdf file (from app_data directory output directory - typically .\bin\debug - app runs) , most likely, insert works fine - you're looking @ wrong .mdf file in end!
if want stick approach, try putting breakpoint on myconnection.close() call - , inspect .mdf file sql server mgmt studio express - i'm data there.
the real solution in opinion
install sql server express (and you've done anyway)
install sql server management studio express
create database in ssms express, give logical name (e.g.
inventorydb)connect using logical database name (given when create on server) - , don't mess around physical database files , user instances. in case, connection string like:
data source=.\\sqlexpress;database=inventorydb;integrated security=trueand else exactly same before...
also see aaron bertrand's excellent blog post bad habits kick: using attachdbfilename more background info.
Comments
Post a Comment