sql server - VB.NET database data passing error -
i error message
"error while inserting record o table… incorrect syntax near keyword ‘return’.incorrect syntax near ‘,’ ."
public class form10 dim con new sqlclient.sqlconnection dim cmd, com new sqlclient.sqlcommand dim sqlda new sqlclient.sqldataadapter dim ds new dataset private sub form10_load(byval sender system.object, byval e system.eventargs) handles mybase.load try con.connectionstring = "data source=.\sqlexpress;initial catalog =pharmacy;integrated security =true" catch ex exception messagebox.show(ex.message, "connection error", messageboxbuttons.retrycancel, messageboxicon.error) end try end sub private sub btnclear_click(byval sender system.object, byval e system.eventargs) handles btnclear.click txtdcode.text = "" txtrinvo.text = "" txtcreg.text = "" txtprice.text = "" txtqty.text = "" txttamount.text = "" datetimepicker1.text = "" end sub private sub btnsearch_click(byval sender system.object, byval e system.eventargs) handles btnsearch.click con.open() try sqlda = new system.data.sqlclient.sqldataadapter("select * return r_invoice_no='" & txtrinvo.text & "'", con) ds.clear() sqlda.fill(ds, "return") txtdcode.text = ds.tables("return").rows(0).item(0) txtcreg.text = ds.tables("return").rows(0).item(1) txtprice.text = ds.tables("return").rows(0).item(2) txtqty.text = ds.tables("return").rows(0).item(3) txttamount.text = ds.tables("return").rows(0).item(4) datetimepicker1.text = ds.tables("return").rows(0).item(5) progressbar1.visible = true progressbar1.minimum = 0 progressbar1.maximum = 10000 progressbar1.value = 0 dim integer = progressbar1.minimum progressbar1.maximum progressbar1.value = next progressbar1.visible = false catch ex indexoutofrangeexception msgbox("type correct return invoice number search.." & ex.message, msgboxstyle.critical, "try again") con.close() end try end sub private sub btnadd_click(byval sender system.object, byval e system.eventargs) handles btnadd.click con.open() try if txtdcode.text = "" or txtrinvo.text = "" or txtcreg.text = "" or txtprice.text = "" or txtqty.text = "" or txttamount.text = "" or datetimepicker1.text = "" msgbox("you must have fill book details.") else dim strinst string = "insert return(drug_code,c_reg_no,sale_price,qty,tot_amount,date,r_invoice_no)values('" & txtdcode.text & "','" & txtcreg.text & "','" & txtprice.text & "','" & txtqty.text & "','" & txttamount.text & "','" & datetimepicker1.text & "','" & txtrinvo.text & "')" dim cmd_insert new system.data.sqlclient.sqlcommand(strinst, con) cmd_insert.executenonquery() progressbar1.visible = true progressbar1.minimum = 0 progressbar1.maximum = 10000 progressbar1.value = 0 dim integer = progressbar1.minimum progressbar1.maximum progressbar1.value = next msgbox("new recored has been added", msgboxstyle.information) progressbar1.visible = true txtdcode.text = "" txtrinvo.text = "" txtcreg.text = "" txtprice.text = "" txtqty.text = "" txttamount.text = "" datetimepicker1.text = "" end if catch ex exception messagebox.show("error while inserting record on table..." & ex.message, "error inserting") con.close() end try end sub private sub btnupdate_click(byval sender system.object, byval e system.eventargs) handles btnupdate.click try con.open() com.connection = con com.commandtext = "update return set drug_code ='" & txtdcode.text & "',c_reg_no='" & txtcreg.text & "',sale_price='" & txtprice.text & "',qty='" & txtqty.text & "',tot_amount='" & txttamount.text & "',date='" & datetimepicker1.text & "'" com.executenonquery() progressbar1.visible = true progressbar1.minimum = 0 progressbar1.maximum = 10000 progressbar1.value = 0 dim integer = progressbar1.minimum progressbar1.maximum progressbar1.value = next msgbox("your rocord has been updated", msgboxstyle.information, "update records") progressbar1.visible = false txtdcode.text = "" txtrinvo.text = "" txtcreg.text = "" txtprice.text = "" txtqty.text = "" txttamount.text = "" datetimepicker1.text = "" catch ex exception messagebox.show("error while updating password on table..." & ex.message, "insert records") con.close() end try end sub private sub btndelete_click(byval sender system.object, byval e system.eventargs) handles btndelete.click try con.open() com.connection = con com.commandtext = "delete return r_invoice_no=" & txtrinvo.text com.executenonquery() progressbar1.visible = true progressbar1.minimum = 0 progressbar1.maximum = 10000 progressbar1.value = 0 dim integer = progressbar1.minimum progressbar1.maximum progressbar1.value = next msgbox("records deleted return invoice number..." & txtrinvo.text, msgboxstyle.information, "record deleted.") progressbar1.visible = false txtdcode.text = "" txtrinvo.text = "" txtcreg.text = "" txtprice.text = "" txtqty.text = "" txttamount.text = "" datetimepicker1.text = "" catch ex invalidcastexception messagebox.show("error while retrieving records on table..." & ex.message, "load records") con.close() end try end sub end class
change line:
dim strinst string = "insert return(drug_code,c_reg_no,sale_price,qty,tot_amount,date,r_invoice_no)values('" & txtdcode.text & "','" & txtcreg.text & "','" & txtprice.text & "','" & txtqty.text & "','" & txttamount.text & "','" & datetimepicker1.text & "','" & txtrinvo.text & "')" to this:
dim strinst string = "insert [return](drug_code,c_reg_no,sale_price,qty,tot_amount,date,r_invoice_no)values('" & txtdcode.text & "','" & txtcreg.text & "','" & txtprice.text & "','" & txtqty.text & "','" & txttamount.text & "','" & datetimepicker1.text & "','" & txtrinvo.text & "')" notice square brackets around table name. return reserved word in sql server, causing error. recommend change name of table. if not possible, stuck adding square brackets every time use table.
Comments
Post a Comment