updating sql server after changing datagridview

[lleemon]

Right now I have a datagrid (dgvResults) that shows results of all the records in the table CS_Comm. When I make a change to a select record in an editable cell and click the 'Save' button, I get the error 'System.InvalidOperationException' {"The ConnectionString property has not been initialized."} and points to _sdaAdapter.Update(_dt).

Any idea why this is happening?

    Dim _sdaAdapter As SqlDataAdapter
    Dim _dt As DataTable

    Private Sub Commissions_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.Icon = My.Resources.AppIcon
        LoadData()
    End Sub

    Sub LoadData()
        dgvResults.DataSource = Nothing
        dgvResults.Rows.Clear()

        Try
            Dim connection As New SqlConnection
            Dim sqlCommand As New SqlCommand
            connection.ConnectionString = GetConnectionString()
            connection.Open()

            sqlCommand.CommandText = "SELECT * FROM CS_Comm ORDER BY CS_Comm_ID desc"
            sqlCommand.Connection = connection

            sqlCommand.Parameters.Clear()

            _sdaAdapter = New SqlDataAdapter(sqlCommand)
            _dt = New DataTable
            _sdaAdapter.Fill(_dt)

            Dim sqlCB As New SqlCommandBuilder(_sdaAdapter)

            connection.Close()
            connection.Dispose()

            dgvResults.DataSource = _dt
            dgvResults.Columns(0).ReadOnly = True
            dgvResults.Columns(1).ReadOnly = True
            dgvResults.Columns(10).ReadOnly = True
            dgvResults.Columns(11).ReadOnly = True
            dgvResults.Columns(12).ReadOnly = True
            dgvResults.Columns(13).ReadOnly = True

            tslWarning.Text = "Records: " & dgvResults.RowCount - 1
        Catch ex As Exception
            tslWarning.Text = "ERROR: " & ex.ToString
        End Try
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        _sdaAdapter.Update(_dt)
    End Sub

[jmcilhinney]

I would imagine that the issue is here:

connection.Dispose()

DO NOT dispose something that you intend to use again. Disposing an object is akin to destroying it so you should only ever be disposing object that you do not intend to use ever again. Given that you have to use that connection again in order to save data, you obviously intend to use it again.

Also, your opening and closing the connection when loading the data is needless. Just as you're relying on the connection being implicitly opened and closed when you call Update, so you should be when you call Fill too.

[lleemon]

Thanks! That was the issue.

As for Open/Close, would you do it this way instead?

    Dim connection As New SqlConnection(GetConnectionString())
    Dim _sdaAdapter As SqlDataAdapter
    Dim _dt As DataTable

    Private Sub Commissions_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.Icon = My.Resources.AppIcon
        connection.Open()
        LoadData()
    End Sub

    Private Sub Commissions_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        connection.Close()
        connection.Dispose()
    End Sub

    Sub LoadData()
        dgvResults.DataSource = Nothing
        dgvResults.Rows.Clear()

        Try

            Dim sqlCommand As New SqlCommand
            sqlCommand.CommandText = "SELECT * FROM CS_Comm ORDER BY CS_Comm_ID desc"
            sqlCommand.Connection = connection
            sqlCommand.Parameters.Clear()

            _sdaAdapter = New SqlDataAdapter(sqlCommand)
            _dt = New DataTable
            _sdaAdapter.Fill(_dt)

            Dim sqlCB As New SqlCommandBuilder(_sdaAdapter)

            dgvResults.DataSource = _dt
            dgvResults.Columns(0).ReadOnly = True
            dgvResults.Columns(1).ReadOnly = True
            dgvResults.Columns(10).ReadOnly = True
            dgvResults.Columns(11).ReadOnly = True
            dgvResults.Columns(12).ReadOnly = True
            dgvResults.Columns(13).ReadOnly = True

            tslWarning.Text = "Records: " & dgvResults.RowCount - 1
        Catch ex As Exception
            tslWarning.Text = "ERROR: " & ex.ToString
        End Try
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        _sdaAdapter.Update(_dt)
    End Sub

[marniel647]

I always put the open connection after my query or before i invoke the ExecuteNonQuery, etc of the command class in ADO but still the same when using SQL. so i suggest that you put the connection.open before the command and after that the connection.close

[jmcilhinney]

As for Open/Close, would you do it this way instead?

Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data to see how I would do it.

[lleemon]

jmcilhinney - do you have an example if your command is joining two tables? Getting 'Dynamic SQL generation is not supported against multiple base tables.' right now when I try to join two tables.

Dim connection As New SqlConnection(GetConnectionString())
    Dim _sdaAdapter As New SqlDataAdapter("SELECT cc.*, c.name FROM CS_Comm cc JOIN customer c ON c.custnum = cc.custnum ORDER BY ID desc", connection)
    Dim sqlCB As New SqlCommandBuilder(_sdaAdapter)
    Dim _dt As New DataTable

    Private Sub Commissions_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.Icon = My.Resources.AppIcon
        LoadData()
        Me.Height = 454
    End Sub

    Private Sub InitialiseDataAdapter()
        Dim sUpdate As String = "UPDATE CS_Commissions SET " _
                                & "invoicenum = @INVOICENUM " _
                                & "total_sales = @TOTAL_SALES " _
                                & "commission = @COMMISSION " _
                                & "contract_type = @contract_type " _
                                & "contract_duration = @contract_duration " _
                                & "contract_amt = @CONTRACT_AMT " _
                                & "contract_commission = @CONTRACT_COMMISSION " _
                                & "remarks = @remarks " _
                                & "WHERE ID = @ID"
        Dim sqlUpdate As New SqlCommand(sUpdate, Me.connection)

        sqlUpdate.Parameters.Add("@INVOICENUM", SqlDbType.Int, 4, "invoicenum")

        Dim pTotalSales As New SqlParameter("@TOTAL_SALES", SqlDbType.Decimal)
        pTotalSales.Value = "total_sales"
        pTotalSales.Precision = 18
        pTotalSales.Scale = 2
        sqlUpdate.Parameters.Add(pTotalSales)

        Dim pCommission As New SqlParameter("@COMMISSION", SqlDbType.Decimal)
        pCommission.Value = "commission"
        pCommission.Precision = 18
        pCommission.Scale = 2
        sqlUpdate.Parameters.Add(pCommission)

        sqlUpdate.Parameters.Add("@contract_type", SqlDbType.VarChar, 2, "contract_type")
        sqlUpdate.Parameters.Add("@contract_duration", SqlDbType.Int, 4, "contract_duration")

        Dim pContractAmt As New SqlParameter("@CONTRACT_AMT", SqlDbType.Decimal)
        pContractAmt.Value = "contract_amt"
        pContractAmt.Precision = 18
        pContractAmt.Scale = 2
        sqlUpdate.Parameters.Add(pContractAmt)

        Dim pContractCommission As New SqlParameter("@CONTRACT_COMMISSION", SqlDbType.Decimal)
        pContractCommission.Value = "contract_commission"
        pContractCommission.Precision = 18
        pContractCommission.Scale = 2
        sqlUpdate.Parameters.Add(pContractCommission)

        sqlUpdate.Parameters.Add("@remarks", SqlDbType.VarChar, 8000, "remarks")
        sqlUpdate.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")

        Me._sdaAdapter.UpdateCommand = sqlUpdate

        Me._sdaAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    End Sub

    Sub LoadData()
        Try
            _sdaAdapter.Fill(_dt)

            dgvResults.DataSource = _dt
            dgvResults.Columns(0).ReadOnly = True
            dgvResults.Columns(1).ReadOnly = True
            dgvResults.Columns(2).ReadOnly = True
            dgvResults.Columns(10).ReadOnly = True
            dgvResults.Columns(11).ReadOnly = True
            dgvResults.AutoResizeColumns()

            tslWarning.Text = "Records: " & dgvResults.RowCount - 1
        Catch ex As Exception
            MessageBox.Show("ERROR: " & ex.ToString)
        End Try
    End Sub

    Private Sub SaveData()
        _sdaAdapter.Update(_dt)
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        SaveData()
    End Sub

[jmcilhinney]

jmcilhinney - do you have an example if your command is joining two tables? Getting 'Dynamic SQL generation is not supported against multiple base tables.' right now when I try to join two tables.

As the error message says, the system cannot generate SQL code to INSERT, UPDATE and DELETE if the SELECT statement involves more than one table. The same is true for UPDATE and DELETE if it involves a single table but doesn't return a primary key. In those situations you cannot use a command builder so you are obliged to write all the SQL code yourself. There's an example of that in the CodeBank thread I mentioned earlier.

[lleemon]

Are you referring to 'Saving changes directly to one or more records in the database.'? I guess I didn't see an example like this.

[lleemon]

Here is how I got it too work. Not sure if it's the best way but it's a way.

Dim connection As New SqlConnection(GetConnectionString())
    Dim _sdaAdapter As New SqlDataAdapter
    Dim _ds As New DataSet
    Dim _dt As New DataTable

    Private Sub Commissions_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.Icon = My.Resources.AppIcon
        LoadData()
        Me.Height = 454
    End Sub

    Sub LoadData()

        Try

            Dim sSQL As String = "SELECT cc.*, c.name FROM CS_Comm cc JOIN customer c ON c.custnum = cc.custnum ORDER BY ID desc"
            _sdaAdapter.SelectCommand = New SqlCommand(sSQL, connection)
            _ds.Tables.Add(New DataTable("updatecommissions"))
            _sdaAdapter.Fill(_ds.Tables("updatecommissions"))

            dgvResults.DataSource = _ds.Tables("updatecommissions")
            dgvResults.Columns(0).HeaderText = "ID"
            dgvResults.Columns(0).ReadOnly = True
            dgvResults.Columns(1).ReadOnly = True
            dgvResults.Columns(1).HeaderText = "CustNum"
            dgvResults.Columns(2).ReadOnly = True
            dgvResults.Columns(2).HeaderText = "SalesRep"
            dgvResults.Columns(4).HeaderText = "Total Sales"
            dgvResults.Columns(5).HeaderText = "Commission"
            dgvResults.Columns(6).HeaderText = "Contact Type"
            dgvResults.Columns(7).HeaderText = "Contact Duraction"
            dgvResults.Columns(8).HeaderText = "Contact Amt"
            dgvResults.Columns(9).HeaderText = "Contact Commission"
            dgvResults.Columns(10).ReadOnly = True
            dgvResults.Columns(10).HeaderText = "Remarks"
            dgvResults.Columns(11).ReadOnly = True
            dgvResults.Columns(11).HeaderText = "Entry Dt"
            dgvResults.AutoResizeColumns()

            tslWarning.Text = "Records: " & dgvResults.RowCount - 1
        Catch ex As Exception
            MessageBox.Show("ERROR: " & ex.ToString)
        End Try
    End Sub

    Private Sub SaveData()

        If _ds.HasChanges Then
            Dim sUpdateSQL As String = "UPDATE CS_Commissions SET " _
                                & "invoicenum = @INVOICENUM, " _
                                & "total_sales = @TOTAL_SALES, " _
                                & "commission = @COMMISSION, " _
                                & "contract_type = @contract_type, " _
                                & "contract_duration = @contract_duration, " _
                                & "contract_amt = @CONTRACT_AMT, " _
                                & "contract_commission = @CONTRACT_COMMISSION, " _
                                & "remarks = @remarks " _
                                & "WHERE ID = @ID"

            _sdaAdapter.UpdateCommand = New SqlCommand(sUpdateSQL, connection)

            With _sdaAdapter
                .UpdateCommand.Parameters.Add("@INVOICENUM", SqlDbType.Int, 4).SourceColumn = "invoicenum"

                Dim pTotalSales As New SqlParameter("@TOTAL_SALES", SqlDbType.Decimal)
                'pTotalSales.Value = "total_sales"
                pTotalSales.Precision = 18
                pTotalSales.Scale = 2
                .UpdateCommand.Parameters.Add(pTotalSales).SourceColumn = "total_sales"

                Dim pCommission As New SqlParameter("@COMMISSION", SqlDbType.Decimal)
                'pCommission.Value = "commission"
                pCommission.Precision = 18
                pCommission.Scale = 2
                .UpdateCommand.Parameters.Add(pCommission).SourceColumn = "commission"

                .UpdateCommand.Parameters.Add("@contract_type", SqlDbType.VarChar, 2).SourceColumn = "contract_type"
                .UpdateCommand.Parameters.Add("@contract_duration", SqlDbType.Int, 4).SourceColumn = "contract_duration"

                Dim pContractAmt As New SqlParameter("@CONTRACT_AMT", SqlDbType.Decimal)
                'pContractAmt.Value = "contract_amt"
                pContractAmt.Precision = 18
                pContractAmt.Scale = 2
                .UpdateCommand.Parameters.Add(pContractAmt).SourceColumn = "contract_amt"

                Dim pContractCommission As New SqlParameter("@CONTRACT_COMMISSION", SqlDbType.Decimal)
                'pContractCommission.Value = "contract_commission"
                pContractCommission.Precision = 18
                pContractCommission.Scale = 2
                .UpdateCommand.Parameters.Add(pContractCommission).SourceColumn = "contract_commission"

                .UpdateCommand.Parameters.Add("@remarks", SqlDbType.VarChar, 8000).SourceColumn = "remarks"
                .UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 4, "ID").SourceVersion = DataRowVersion.Original
            End With
            _sdaAdapter.Update(_ds.Tables("updatecommissions"))
        End If

    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        SaveData()
    End Sub