sql - vb2008 insert and update record using one button -

sql - vb2008 insert and update record using one button -

i trying create glossary. have form listbox, 2 textboxes, , save button.

the listbox populated words database, , when word selected, definition display in textbox2.

the user can add together record filling textbox1 new word , textbox2 definition,and clicking save button. if new word existed not allow save new record, if there's null value between 2 textboxes. if doesn't exist inserted on table , new word added listbox.

the user can update record selecting first word on list edit word and/or definition , clicking save button.

i got updating part work have problem in inserting new record. can't properly. glossary table has 2 fields: word, definition. here's code:

dim mycmd new mysqlcommand dim myreader mysqldatareader dim myadptr new mysqldataadapter dim mydatatable new datatable private sub btnsave_click(byval sender system.object, byval e system.eventargs) handles btnsave.click phone call connect() me if blank() = false if duplicate() = false strsql = "insert glossary values (@word, @def)" mycmd.connection = myconn mycmd.commandtext = strsql mycmd.parameters.addwithvalue("word", txtnew.text) mycmd.parameters.addwithvalue("def", txtdefine.text) mycmd.executenonquery() mycmd.dispose() msgbox("record added") dim word string word = txtnew.text lstword.items.add(word) 'myconn.close() 'me.filllistbox() else myconn.open() strsql = "update glossary set word = @term, definition = @mean word = @term" mycmd.connection = myconn mycmd.commandtext = strsql mycmd.parameters.addwithvalue("term", txtnew.text) mycmd.parameters.addwithvalue("mean", txtdefine.text) mycmd.executenonquery() mycmd.dispose() msgbox("record updated", msgboxstyle.information, "new word added") end if end if end end sub public function blank() boolean phone call connect() me if .txtnew.text = "" or .txtdefine.text = "" blank = true msgbox("cannot save! term , definition should not contain null value", msgboxstyle.critical, "unable save") else blank = false end if end end function public function duplicate() boolean phone call connect() me strsql = "select * glossary word = '" & txtnew.text & "'" mycmd.connection = myconn mycmd.commandtext = strsql if mydatatable.rows.count <> 0 duplicate = true 'msgbox("word exist. please check word.", msgboxstyle.critical, "duplicate.") else duplicate = false end if myconn.close() end end function

this connection module:

public myconnectionstring string public strsql string public myconn new mysqlconnection public sub connect() myconn seek if .state = connectionstate.open .close() end if myconnectionstring = "database=firstaidcqs;server=localhost;uid=root;password=" .connectionstring = myconnectionstring .open() 'msgbox("successful connection") grab ex exception msgbox(ex.message, msgboxstyle.critical, "connection error") .close() end seek end end sub public sub disconnect() myconn .close() .dispose() end end sub

help me create work properly. have finish tomorrow. thought much appreciated.

you using global variables in code above.

myconn , mycmd

in particular, phone call dispose on mycmd can't see anywhere reinitialization of object new. also, forgetting moment mycmd.dispose problem, don't reset mycmd parameters collection. in way end wrong parameter collection command executed, don't forget open connection insert part. (and close both parts)

you avoid utilize of unnecessary global variables

.... if duplicate() = false strsql = "insert glossary values (@word, @def)" using mycmd = new mysqlcommand(strsql, myconn) myconn.open() mycmd.parameters.addwithvalue("word", txtnew.text) mycmd.parameters.addwithvalue("def", txtdefine.text) mycmd.executenonquery() end using myconn.close() ..... else strsql = "update glossary set word = @term, definition = @mean word = @term" using mycmd = new mysqlcommand(strsql, myconn) myconn.open() mycmd.parameters.addwithvalue("term", txtnew.text) mycmd.parameters.addwithvalue("mean", txtdefine.text) mycmd.executenonquery() end using myconn.close() .....

a improve solution changing connect() method homecoming initialized connection instead of using global variable. in way enclose creation , destruction of connection in using statement

using myconn mysqlconnection = connect() ....... end using

for work need alter code of connect in way

public function connect() mysqlconnection dim myconn mysqlconnection myconnectionstring = "database=firstaidcqs;server=localhost;uid=root;password=" myconn = new mysqlconnection(myconnectionstring) myconn.open() homecoming myconn end sub

no need of disconnect function because utilize using statement close connection you, no need have global variable maintain connection because reopen connection every time need , close afterward. don't think not performant because ado.net implements connection pooling (it msdn article sqlserver, concept applies mysql)

sql vb.net