Update SQL Server Using SQL Command Object that has Join -

Update SQL Server Using SQL Command Object that has Join -

i trying head around info access using vb.net 2010.

i building dummy application of contacts. have working sql command object handle add, update , delete inly supports single table queries. know have set commands manually if there bring together not sure how.

here current code retrieves info contact table , and contact type table

private sub frmcontacts_load(sender system.object, e system.eventargs) handles mybase.load seek bloading = true 'variable denote form loading modmain.initalisedbconnection() ' open connection database using public connection "cn" sqladapter = new sqldataadapter(ssqlcontactsjoined, cn) sqladapter.fill(datblcontacts) 'fill datatable result of ssqlcontactsjoined cmdbuilder = new sqlcommandbuilder(sqladapter) 'generate add, update , delete statements dgrdcontacts.datasource = datblcontacts formatgrid() 'set headers , hide id columns on datagrid fillcombobox() 'fill combobox contacttypes bloading = false grab ex exception messagebox.show(ex.message) end seek end sub

the value of ssqlcontactsjoined :

dim ssqlcontactsjoined string = "select dbo.tblcontacts.*, dbo.tblcontacttype.contacttype " _ & " dbo.tblcontacts inner bring together dbo.tblcontacttype on dbo.tblcontacts.contacttypeid = dbo.tblcontacttype.contacttypeid"

any pointers tutorial may show me how manually come in add together update & delete statements commandbulder?

if query includes bring together don't utilize command builder @ all. have create sqlcommand objects , assign them insertcommand, updatecommand , deletecommand properties of info adapter explicitly, e.g.

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 info access issue.' end sub private sub savedata() 'save changes.' me.adapter.update(me.table) end sub

sql sql-server vb.net sqlcommandbuilder