Datagridview INSERT/SELECT/DELETE fail

Question

I'm trying to create a master-detail form with datagridviews all in code. The SELECT/fill part is working fine, but I am having trouble with UPDATE/INSERT/DELETE (which should happen automatically when the form is closed). The following code gives the error:

Update requires the UpdateCommand to have a connection object. The Connection property of the UpdateCommand has not been initialized.

on the .Update line.

I tried moving the connection out of the GetData() procedure, but that was not the answer.

What should I be doing?

Code:

Imports System.Data
Imports System.Data.OleDb

Public Class TestMe2

  Private lblSelector As New Label
  Private cbSelector As New ComboBox
  Private bsSelector As New BindingSource
  Private daMaster As New OleDbDataAdapter
  Private dgMaster As New DataGrid
  Private bsMaster As New BindingSource
  Private dgLookup As New DataGrid
  Private bsLookups As New BindingSource
  Private dsGFF As New DataSet
  Private cnn As New OleDbConnection

  Public Sub New()

    ' This call is required by the designer.
    InitializeComponent()

    ' Add any initialization after the InitializeComponent() call.
    Me.Controls.Add(lblSelector)
    lblSelector.Location = New System.Drawing.Point(0, 0)
    lblSelector.Text = "FileType"
    Me.Controls.Add(cbSelector)
    cbSelector.Location = New System.Drawing.Point(Me.lblSelector.Width, 0)
    Me.Controls.Add(dgMaster)
    dgMaster.Location = New System.Drawing.Point(0, cbSelector.Height)
    dgMaster.Height = 300
    dgMaster.Width = 2000
    Me.Controls.Add(dgLookup)
    dgLookup.Location = New System.Drawing.Point(0, cbSelector.Height + dgMaster.Height)
    dgLookup.Height = 100
    dgLookup.Width = 1000

  End Sub

  Private Sub TestMe2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    bsSelector.DataSource = dsGFF
    cbSelector.DataSource = bsSelector
    bsMaster.DataSource = dsGFF
    dgMaster.DataSource = bsMaster
    bsLookups.DataSource = dsGFF
    dgLookup.DataSource = bsLookups
    GetData()
    bsMaster.DataSource = bsSelector
    bsMaster.DataMember = "FileTypesToGFFTranslator"
    bsLookups.DataSource = bsMaster
    bsLookups.DataMember = "GFFTranslatorToTranslations"
    'dgMaster.autoresizecolumns()

  End Sub

  Private Sub GetData()

    Dim command As New OleDbCommand
    Dim parameter As New OleDbParameter

    ' where's the data?
    Dim cnnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\GFFTranslator.accdb"
    cnn = New OleDb.OleDbConnection(cnnString)

    ' set up up the selector
    Dim daSelector As New OleDbDataAdapter("SELECT FileType FROM vwFileTypeSelection", cnn)
    daSelector.Fill(dsGFF, "vwFileTypeSelection")
    bsSelector.DataSource = dsGFF
    bsSelector.DataMember = "vwFileTypeSelection"
    cbSelector.ValueMember = "FileType"
    cbSelector.DisplayMember = "FileType"
    daSelector.Fill(dsGFF)

    ' set up the master
    daMaster = New OleDbDataAdapter("SELECT * FROM GFFTranslator", cnn)
    daMaster.Fill(dsGFF, "GFFTranslator")
    daMaster.UpdateCommand = New OleDbCommand("UPDATE GFFTRanslator" & _
                                              "   SET FileType = ?" & _
                                              "     , FieldPosition = ?" & _
                                              "     , FieldType = ?" & _
                                              "     , StartRepeatingSection = ?" & _
                                              "     , FileTypeIdentifier = ?" & _
                                              "     , Flag = ?" & _
                                              "     , DataStart = ?" & _
                                              "     , DataLength = ?" & _
                                              "     , NextLine = ?" & _
                                              "     , Lookup = ?" & _
                                              "     , Title = ?" & _
                                              "     , ExtraInfo = ?" & _
                                              " WHERE FileType = ?" & _
                                              "   AND FieldPosition = ? ;")
    daMaster.UpdateCommand.Parameters.Add("@FileType", OleDbType.VarChar, 255, "FileType")
    daMaster.UpdateCommand.Parameters.Add("@FieldPosition", OleDbType.Integer, 4, "FieldPosition")
    daMaster.UpdateCommand.Parameters.Add("@FieldType", OleDbType.VarChar, 255, "FieldType")
    daMaster.UpdateCommand.Parameters.Add("@StartRepeatingSection", OleDbType.Boolean, 1, "StartRepeatingSection")
    daMaster.UpdateCommand.Parameters.Add("@FileTypeIdentifier", OleDbType.Boolean, 1, "FileTypeIdentifier")
    daMaster.UpdateCommand.Parameters.Add("@Flag", OleDbType.Boolean, 1, "Flag")
    daMaster.UpdateCommand.Parameters.Add("@DataStart", OleDbType.Integer, 5, "DataStart")
    daMaster.UpdateCommand.Parameters.Add("@DataLength", OleDbType.Integer, 5, "DataLength")
    daMaster.UpdateCommand.Parameters.Add("@NextLine", OleDbType.Boolean, 1, "NextLine")
    daMaster.UpdateCommand.Parameters.Add("@Lookup", OleDbType.Boolean, 1, "Lookup")
    daMaster.UpdateCommand.Parameters.Add("@Title", OleDbType.VarChar, 255, "Title")
    daMaster.UpdateCommand.Parameters.Add("@ExtraInfo", OleDbType.VarChar, 255, "ExtraInfo")

    ' set up the lookup
    Dim daLookup As New OleDbDataAdapter("SELECT * FROM Translations", cnn)
    daLookup.Fill(dsGFF, "Translations")

    ' link everything together
    Try
      Dim drMaster As New DataRelation("FileTypesToGFFTranslator" _
                                      , dsGFF.Tables("vwFileTypeSelection").Columns("FileType") _
                                      , dsGFF.Tables("GFFTranslator").Columns("FileType"))
      dsGFF.Relations.Add(drMaster)

      Dim dcMaster As DataColumn() = New DataColumn() {dsGFF.Tables("GFFTranslator").Columns("FileType"), dsGFF.Tables("GFFTranslator").Columns("FieldType")}
      Dim dcLookup As DataColumn() = New DataColumn() {dsGFF.Tables("Translations").Columns("FileType"), dsGFF.Tables("Translations").Columns("FieldType")}
      Dim drLookup As New DataRelation("GFFTranslatorToTranslations" _
                                      , dcMaster _
                                      , dcLookup)
      dsGFF.Relations.Add(drLookup)
    Catch ex As Exception
      MessageBox.Show("Error: " & ex.Message)
    End Try

  End Sub

  Protected Overrides Sub Finalize()
    Me.Validate()

    Me.daMaster.Update(dsGFF.Tables("GFFTranslator"))
    Me.dsGFF.AcceptChanges()

    MyBase.Finalize()
  End Sub

End Class

Datagridview INSERT/SELECT/DELETE fail

Answers (1)

Related Questions