database - Saving data in datagridview so that it persists after closing program -
i know there's lot on topic, couldn't seem take information , customize code. i'm having problem saving data data persists when close program , open again. it's simple program data gets entered through text boxes data grid view. @ first nothing happening, after hours of working on code i'm starting error message on dataadapter.update(dt) line:
dynamic sql generation not supported against multiple base tables.
i know code isn't following best practice rules, i'm new programming play nice. in advance, appreciate help.
here's code:
public class frmgradebook 'data grid view recording students grades dim connstr string = "provider=microsoft.ace.oledb.12.0;" & _ "data source=gradebook.accdb" dim sqlstr string = "select firstname,lastname,firstexam,secondexam,finalexam " & _ "from students inner join grades on students.studentid = grades.studentid " dim dataadapter new oledb.oledbdataadapter(sqlstr, connstr) dim dt new datatable() 'secondary data grid view displaying students grade dim connstr1 string = "provider=microsoft.ace.oledb.12.0;" & _ "data source=gradebook.accdb" dim sqlstr1 string = "select firstname, lastname, finalexam students inner join grades on students.studentid = grades.studentid " dim dataadapter1 new oledb.oledbdataadapter(sqlstr1, connstr1) dim dt1 new datatable() private sub frmgradebook_load(sender object, e eventargs) handles mybase.load dataadapter.fill(dt) dgvrecordgrades.datasource = dt dataadapter1.fill(dt1) dgvdisplaygrades.datasource = dt1 dgvdisplaygrades.visible = false lstgrade.visible = false end sub private sub button1_click(sender object, e eventargs) handles btnrecord.click dgvdisplaygrades.visible = false dgvrecordgrades.visible = true lstgrade.visible = false 'validates data dim studentid string = txtid.text.toupper dim row integer dim datagridrow datagridviewrow = dgvrecordgrades.rows(1) if isnumeric(txtfirstexam.text) , isnumeric(txtsecexam.text) , isnumeric(txtfinalexam.text) if (txtfirstexam.text).tostring().indexof(".") = -1 dim firstexam double = cdbl(txtfirstexam.text) dim secondexam double = cdbl(txtsecexam.text) dim finalexam double = cdbl(txtfinalexam.text) if firstexam <= 100 , secondexam <= 100 , finalexam <= 100 if firstexam >= 0 , secondexam >= 0 , finalexam >= 0 if studentid = "aj-123456" row = 0 elseif studentid = "fa-192837" row = 1 elseif studentid = "gg-567876" row = 2 elseif studentid = "gj-987654" row = 3 elseif studentid = "ls-222333" row = 4 else messagebox.show("the student id provided not valid. please try again.") end if 'records grades dgvrecordgrades.item(2, row).value = firstexam dgvrecordgrades.item(3, row).value = secondexam dgvrecordgrades.item(4, row).value = finalexam else messagebox.show("invalid input. please try again") end if else messagebox.show("invalid input. please try again") end if else messagebox.show("please enter whole numbers. try again.") end if else messagebox.show("invalid input. please try again") end if end sub private sub button1_click_2(sender object, e eventargs) handles btnpostable.click dgvrecordgrades.visible = false dgvdisplaygrades.visible = false lstgrade.visible = true lstgrade.items.clear() if not isdbnull((dgvdisplaygrades.item(2, 0).value)) lstgrade.items.add("3456 " & lettergrade(cdbl(dgvdisplaygrades.item(2, 0).value))) else lstgrade.items.add("3456 no grade available") end if if not isdbnull((dgvdisplaygrades.item(2, 1).value)) lstgrade.items.add("2837 " & lettergrade(cdbl(dgvdisplaygrades.item(2, 1).value))) else lstgrade.items.add("2837 no grade available") end if if not isdbnull((dgvdisplaygrades.item(2, 2).value)) lstgrade.items.add("7876 " & lettergrade(cdbl(dgvdisplaygrades.item(2, 2).value))) else lstgrade.items.add("7876 no grade available") end if if not isdbnull((dgvdisplaygrades.item(2, 3).value)) lstgrade.items.add("7654 " & lettergrade(cdbl(dgvdisplaygrades.item(2, 3).value))) else lstgrade.items.add("7654 no grade available") end if if not isdbnull((dgvdisplaygrades.item(2, 4).value)) lstgrade.items.add("2333 " & lettergrade(cdbl(dgvdisplaygrades.item(2, 4).value))) else lstgrade.items.add("2333 no grade available") end if end sub private sub button1_click_1(sender object, e eventargs) handles btndisplay.click dgvdisplaygrades.visible = true dgvrecordgrades.visible = false lstgrade.visible = false col integer = 2 2 row integer = 0 4 if not isdbnull(dgvrecordgrades.item(col, row).value) dim firstexam double = cdbl(dgvrecordgrades.item(2, row).value) dim secexam double = cdbl(dgvrecordgrades.item(3, row).value) dim finalexam double = cdbl(dgvrecordgrades.item(4, row).value) dgvdisplaygrades.item(2, row).value = semavg(firstexam, secexam, finalexam) else end if next next end sub function semavg(firstmid double, secondmid double, final double) double dim semaverage double semaverage = (firstmid + secondmid + (2 * final)) / 4 math.round(semaverage) return semaverage end function function lettergrade(semavg double) string select case semavg case >= 90 return "a" case >= 80 return "b" case >= 70 return "c" case >= 60 return "d" case else return "f" end select end function private sub btnsave_click(sender object, e eventargs) handles btnsave.click dim changes integer dim commandbuilder new _ oledb.oledbcommandbuilder(dataadapter) changes = dataadapter.update(dt) if changes > 0 messagebox.show(changes & " changed rows.") else messagebox.show("no changes made.") end if end sub private sub frmgradebook_formclosing(sender object, e formclosingeventargs) handles mybase.formclosing dataadapter.dispose() dataadapter1.dispose() end sub end class
i think following problem.
you filling datatable dt dataadapter contains following
commandtext=
"select firstname,lastname,firstexam,secondexam,finalexam " & _ "from students inner join grades on students.studentid = grades.studentid " which retrieving data two table.
and automatic update command dataadapter, have retrieve data only one table having primary key.
you can't retrieve more 1 table use dataadapter.updatecommand property.
hope helps.
Comments
Post a Comment