vb.net - How to Edit Datagrid Records and also in database -
this code loading data database datagrid
private sub records_load(byval sender system.object, byval e system.eventargs) handles mybase.load dim connstring string = "provider=microsoft.ace.oledb.12.0; data source=" & my.application.info.directorypath.tostring() & "\backup\database3.accdb;" dim myconn oledbconnection dim da oledbdataadapter dim ds dataset dim tables datatablecollection dim source1 new bindingsource myconn = new oledbconnection myconn.connectionstring = connstring ds = new dataset tables = ds.tables da = new oledbdataadapter("select * [userinfo] order id", myconn) da.fill(ds, "userinfo") 'change items database name dim cb = new oledbcommandbuilder(da) dim view new dataview(tables(0)) source1.datasource = view datagridview1.datasource = view end sub private sub btncreate_click(byval sender system.object, byval e system.eventargs) handles btncreate.click if requiredentry() = true return end if try dim cn new oledbconnection("provider=microsoft.ace.oledb.12.0; data source=" & my.application.info.directorypath.tostring() & "\backup\database3.accdb;") if cn.state = connectionstate.closed cn.open() dim ssql string = "insert [userinfo]([username],[password],[firstname],[lastname],[account]) values(@username,@password,@firstname,@lastname,@account)" dim cmd oledbcommand = new oledbcommand(ssql, cn) ' username if txtpassword.text = txtconfirm.text dim username oledbparameter = new oledbparameter("@username", oledbtype.varwchar, 50) username.value = txtuser.text.tostring() cmd.parameters.add(username) else msgbox("password not matched") end if 'password dim password oledbparameter = new oledbparameter("@password", oledbtype.varwchar, 50) password.value = txtpassword.text.tostring() cmd.parameters.add(password) 'first name dim firstname oledbparameter = new oledbparameter("@firstname", oledbtype.varwchar, 50) firstname.value = txtfirstname.text.tostring() cmd.parameters.add(firstname) ' last name dim lastname oledbparameter = new oledbparameter("@lastname", oledbtype.varwchar, 50) lastname.value = txtlastname.text.tostring() cmd.parameters.add(lastname) 'account dim account oledbparameter = new oledbparameter("@account", oledbtype.varwchar, 50) account.value = cboaccount.getitemtext(cboaccount.selecteditem) cmd.parameters.add(account) if cmd.executenonquery() cn.close() messagebox.show("new user added successfully.", "record saved") call clear() me.hide() filemaintenance.show() else msgbox("new user addition failed ", msgboxstyle.critical, "addition failed") return end if catch ex exception exit sub end try end sub private sub newuser_keydown(byval sender object, byval e system.windows.forms.keyeventargs) handles me.keydown e.suppresskeypress = e.control if e.keycode = keys.enter sendkeys.send("{tab}") end if end sub and delete
private sub cmddelete_click(byval sender system.object, byval e system.eventargs) handles cmddelete.click dim connstring string = "provider=microsoft.ace.oledb.12.0; data source=" & my.application.info.directorypath.tostring() & "\backup\database3.accdb;" dim myconn oledbconnection dim da oledbdataadapter dim ds dataset dim tables datatablecollection dim source1 new bindingsource dim rows string try myconn = new oledbconnection myconn.connectionstring = connstring ds = new dataset tables = (ds.tables) rows = datagridview1.selectedrows(0).cells(0).value.tostring() da = new oledbdataadapter("delete * [userinfo] id=" & rows, myconn) da.fill(ds, "userinfo") records_load(sender, e) catch ex exception messagebox.show("cannot delete empty records") end try how can edit data datagrid view in database how can lock datagrid entering fields accidentally
your code wrong. you're retrieving data correctly in first place you're inserting , deleting incorrectly.
the idea use 1 data adapter 4 operations, i.e. select, insert, update , delete. that's why has selectcommand, insertcommand, updatecommand , deletecommand properties. call fill , executes sql in selectcommand retrieve data datatable. supposed make changes want, i.e. insert, update , delete, data in datatable. when you're done, call update on data adapter , execute sql in insertcommand, updatecommand , deletecommand required save changes database. here's example wrote long time ago:
private connection new sqlconnection("connection string here") private adapter new sqldataadapter("select id, name, quantity, unit stockitem", _ connection) private table new datatable private sub initialisedataadapter() dim delete new sqlcommand("delete stockitem id = @id", me.connection) dim insert new sqlcommand("insert stockitem (name, quantity, unit) values (@name, @quantity, @unit)", me.connection) dim update new sqlcommand("update stockitem set name = @name, quantity = @quantity, unit = @unit id = @id", me.connection) delete.parameters.add("@id", sqldbtype.int, 4, "id") insert.parameters.add("@name", sqldbtype.varchar, 100, "name") insert.parameters.add("@quantity", sqldbtype.float, 8, "quantity") insert.parameters.add("@unit", sqldbtype.varchar, 10, "unit") update.parameters.add("@name", sqldbtype.varchar, 100, "name") update.parameters.add("@quantity", sqldbtype.float, 8, "quantity") update.parameters.add("@unit", sqldbtype.varchar, 10, "unit") update.parameters.add("@id", sqldbtype.int, 4, "id") me.adapter.deletecommand = delete me.adapter.insertcommand = insert me.adapter.updatecommand = update me.adapter.missingschemaaction = missingschemaaction.addwithkey end sub private sub getdata() 'retrieve data. me.adapter.fill(me.table) 'the table can used here display , edit data. 'that involve data-binding not data access issue. end sub private sub savedata() 'save changes. me.adapter.update(me.table) end sub note example written sql server swap out sqlclient types oledb types , works access.
if want save changes reason call update after each change.
by way, connection string should written this:
dim connstring string = "provider=microsoft.ace.oledb.12.0; data source=|datadirectory|\backup\database3.accdb;"
Comments
Post a Comment