Update SQL Server Using SQL Command Object that has Join

Question

I am trying to get my head around data access using VB.NET 2010.

I am slowly building a dummy application of contacts. I have it working with an SQL command object to handle the ADD, UPDATE and DELETE but it inly supports single table queries. I know I have to set the commands manually if there is a join but not sure how.

Here is my current code that retrieves the data from a Contact table and and a Contact Type Table

    Private Sub frmContacts_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Try
        bloading = True 'variable to denote form is loading
        ModMain.InitaliseDBConnection() ' open connection to database using public connection "cn"

        sqlAdapter = New SqlDataAdapter(sSqlContactsJoined, cn)
        sqlAdapter.Fill(datblContacts) 'fill datatable with result of sSqlContactsJoined
        CmdBuilder = New SqlCommandBuilder(sqlAdapter) 'generate the ADD, UPDATE and DELETE statements

        dgrdContacts.DataSource = datblContacts
        FormatGrid() 'Set the headers and hide ID columns on datagrid
        FillComboBox() 'fill combobox with ContactTypes
        bloading = False
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

End Sub

The value of sSqlContactsJoined is :

Dim sSqlContactsJoined As String = "SELECT dbo.tblContacts.*, dbo.tblContactType.ContactType FROM " _
                                & " dbo.tblContacts INNER JOIN dbo.tblContactType ON dbo.tblContacts.ContactTypeID = dbo.tblContactType.ContactTypeID"

any pointers to a tutorial that may show me how to manually enter the ADD UPDATE & DELETE statements for the CommandBulder?

From MSDN: `SqlcommandBuilder Automatically generates single-table commands ...` — Steve, Jun 20 '14 at 07:28

score 2 · Answer 1 · answered Jun 20 '14 at 08:08

If your query includes a join then you don't use a command builder at all. You have to create SqlCommand objects and assign them to the InsertCommand, UpdateCommand and DeleteCommand properties of the data adapter explicitly, e.g.

Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                      connection)
Private table As New DataTable

Private Sub InitialiseDataAdapter()
    Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
    Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
    Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE 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 the data.'
    Me.adapter.Fill(Me.table)

    'The table can be used here to display and edit the data.'
    'That will most likely involve data-binding but that is not a data access issue.'
End Sub

Private Sub SaveData()
    'Save the changes.'
    Me.adapter.Update(Me.table)
End Sub

Update SQL Server Using SQL Command Object that has Join

1 Answers1