CommandBuilder

[ahbenshaut]

CommandBuilder RESOLVED

Hopefully this will have a easy answer.
Here's the code I am using and it works great. My question is, Is it possible to use INNER and/or OUTER JOINS in the sql string (thereby updating more than one table at a time) and still use this code?

[size=2][color=#0000ff]Private[/color][/size][size=2] m_ds [/size][size=2][color=#0000ff]As[/color][/size][size=2] DataSet[/size]
[size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Const[/color][/size][size=2] sql [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size][size=2] = "SELECT ID, NTLogin, Admin FROM USecurity ORDER By NTLogin"[/size]
[size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] test_Load([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2][color=#0000ff]MyBase[/color][/size][size=2].Load
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] cn [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlConnection(cn)
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] da [/size][size=2][color=#0000ff]As[/color][/size][size=2] SqlDataAdapter
 
da = [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlDataAdapter(sql, cn)
 
da.TableMappings.Add("Table", "Users")
 
m_ds = [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataSet
 
da.Fill(m_ds)
 
DataGrid1.SetDataBinding(m_ds, "Users")
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub
 
[/color][/size][size=2]
 
[/size][size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] btnClose_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] btnClose.Click
 
[/size][size=2][color=#0000ff]If[/color][/size][size=2] m_ds.HasChanges = [/size][size=2][color=#0000ff]True[/color][/size][size=2][color=#0000ff]Then
 
[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] da [/size][size=2][color=#0000ff]As[/color][/size][size=2] SqlDataAdapter
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] cb [/size][size=2][color=#0000ff]As[/color][/size][size=2] SqlCommandBuilder
 
da = [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlDataAdapter(sql, cnnCheck21)
 
da.TableMappings.Add("Table", "Users")
 
cb = [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlCommandBuilder(da)
 
[/size][size=2][color=#008000]' Uncomment this code to see the INSERT,
 
[/color][/size][size=2][color=#008000]' UPDATE, and DELETE commands.
 
[/color][/size][size=2]Debug.WriteLine("*** INSERT ***")
 
Debug.WriteLine(cb.GetInsertCommand.CommandText)
 
Debug.WriteLine("*** UPDATE ***")
 
Debug.WriteLine(cb.GetUpdateCommand.CommandText)
 
Debug.WriteLine("*** DELETE ***")
 
Debug.WriteLine(cb.GetDeleteCommand.CommandText)
 
[/size][size=2][color=#008000]' Save the changes.
 
[/color][/size][size=2]da.Update(m_ds)
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]If
 
[/color][/size][code]

 

[jmcilhinney]

You can retrieve data using a join if you like. To then update each of the joined tables you would still need to use seperate SQL statements. Note that it is possible to include more than one SQL statement in the CommandText propert of an SqlCommand if they are seperated with semicolons.

Edit:
Forgot to say that it is not possible to use any type of auto-generated SQL statements with a query that includes joined tables, whether it be a CommandBuilder or the Data Adapter Wizard. You must write the SQL statements yourself.