Updating SQL table from a bound datagridview

[Halliarse]

Good Morning All

I'm using Visual Studio 2019

I have an issue with a datagridview that I just cannot resolve....after a lot of wasted time!
My datagridview is populated programmatically from an SQL table, and clicking an update button needs to either update the database, or insert new records (I've not looked at this yet!)

When I click the Update button I am getting an error message as shown below. The full code for the form is as below with the error line annotated:

Imports System.Data.SqlClient

Public Class ProductsServices
    Private bindingSource1 As BindingSource
    Public UpdateType As String = String.Empty
    Private con As SqlConnection
    Private cmd As SqlCommand
    Private sda As SqlDataAdapter
    Private ds As DataSet

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles butCancel.Click

        Me.Close()
        Main.Show()

    End Sub

    Private Sub ProductsServices_Load(sender As Object, e As EventArgs) Handles Me.Load

        Try
            dgvProducts.AutoGenerateColumns = True

            Using con As New SqlConnection(Main.DWDataConstr)
                Using cmd As New SqlCommand("Select [Prod ID], [Prod Ref code],[Prod description],[Prod Default Price],[Prod Stock Value],[Prod Delivery Cost],[Prod Weight],[Prod Stock Effect],SEQ,VATRate,RentalPeriod,Active from [Products and services] Order By SEQ, [Prod description]")
                    Using sda As New SqlDataAdapter()
                        sda.SelectCommand = cmd

                        cmd.Connection = con
                        con.Open()

                        Using ds As New DataSet
                            sda.Fill(ds)

                            dgvProducts.DataSource = ds.Tables(0)

                        End Using

                        'con.Close()

                    End Using
                End Using
            End Using

            dgvProducts.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders
            dgvProducts.BorderStyle = BorderStyle.Fixed3D
            dgvProducts.EditMode = DataGridViewEditMode.EditOnEnter

            dgvProducts.RowHeadersVisible = False

            ' Prod ID
            dgvProducts.Columns(0).Visible = False

            'Prod Ref Code
            dgvProducts.Columns(1).Width = 100
            dgvProducts.Columns(1).HeaderCell.Value = "Reference Code"

            ' Prod Description
            dgvProducts.Columns(2).Width = 300
            dgvProducts.Columns(2).HeaderCell.Value = "Product Description"

            ' Prod Default Price
            dgvProducts.Columns(3).Width = 70
            dgvProducts.Columns(3).HeaderCell.Value = "Default Price"
            dgvProducts.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            dgvProducts.Columns(3).DefaultCellStyle.Format = "C2"

            ' Prod Stock Value
            dgvProducts.Columns(4).Width = 70
            dgvProducts.Columns(4).HeaderCell.Value = "Stock Value"
            dgvProducts.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            dgvProducts.Columns(4).DefaultCellStyle.Format = "C2"

            ' Prod Delivery Cost
            dgvProducts.Columns(5).Width = 70
            dgvProducts.Columns(5).HeaderCell.Value = "Delivery Cost"
            dgvProducts.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            dgvProducts.Columns(5).DefaultCellStyle.Format = "C2"

            ' Prod Weight
            dgvProducts.Columns(6).Width = 50
            dgvProducts.Columns(6).HeaderCell.Value = "Weight"
            dgvProducts.Columns(6).HeaderCell.Style.Alignment = DataGridViewContentAlignment.BottomRight
            dgvProducts.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            ' Prod Stock Effect
            dgvProducts.Columns(7).Width = 50
            dgvProducts.Columns(7).HeaderCell.Value = "Stock Effect"
            dgvProducts.Columns(7).HeaderCell.Style.Alignment = DataGridViewContentAlignment.BottomCenter
            dgvProducts.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

            ' Sequence
            dgvProducts.Columns(8).Width = 50
            dgvProducts.Columns(8).HeaderCell.Value = "SEQ"
            dgvProducts.Columns(8).HeaderCell.Style.Alignment = DataGridViewContentAlignment.BottomCenter
            dgvProducts.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

            ' VAT Rate
            dgvProducts.Columns(9).Width = 70
            dgvProducts.Columns(9).HeaderCell.Value = "VAT Rate"
            dgvProducts.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            dgvProducts.Columns(9).DefaultCellStyle.Format = "0.00\%"

            ' Rental Period
            dgvProducts.Columns(10).Width = 50
            dgvProducts.Columns(10).HeaderCell.Value = "Rental Period"
            dgvProducts.Columns(10).HeaderCell.Style.Alignment = DataGridViewContentAlignment.BottomCenter
            dgvProducts.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

            ' Active
            dgvProducts.Columns(11).Width = 50
            dgvProducts.Columns(11).HeaderCell.Value = "Active"

        Catch __unusedSqlException1__ As SqlException
            MessageBox.Show("To be determined", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            System.Threading.Thread.CurrentThread.Abort()
        End Try

    End Sub

    Private Sub butUpdate_Click(sender As Object, e As EventArgs) Handles butUpdate.Click

        Try

            Dim table As New DataTable()

            bindingSource1 = dgvProducts.DataSource ' ERROR LINE HERE

            table = bindingSource1.DataSource

            sda.Update(table)

            con.Close()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

End Class

Any help, advise or a point in the right direction would be gratefully received!!

Thanks

Steve

 

[Andrzejek]

Wouldn't [tt]EditOnEnter[/tt] allow you to edit (update) data in the grid (and in your underlying table) when you hit Enter?

...
dgvProducts.DataSource = ds.Tables(0)
...
dgvProducts.EditMode = DataGridViewEditMode.[blue]EditOnEnter[/blue]
...

---- Andy

There is a great need for a sarcasm font.

 

[Halliarse]

Hi Andy

Thanks for your response.

I'm guessing you mean add that line and remove the Update button? I've tried this and the underlying data is not updating!

Thanks

Steve

 

[Andrzejek]

You already have those lines in your code, I copied them from your post.

You may also investigate [link

https://stackoverflow.com/questions/51359701/update-database-from-data-grid-view-in-vb-net

]this post[/url] of how to update database from data grid view in vb.net


---- Andy

There is a great need for a sarcasm font.

 

[jebenson]

Instead of doing this:

bindingSource1 = dgvProducts.DataSource ' ERROR LINE HERE

table = bindingSource1.DataSource

Just do this:

table = dgvProducts.DataSource

Also, your update is going to fail, because you don't have your DataAdapter's InsertCommand, UpdateCommand or DeletCommand properties set. Here's an example for the InsertCommand, to get you started:

Dim InsCmd As SqlCommand = Nothing
Dim SQLStr As String = ""
Dim FieldList as string = ""
Dim ValuesList as string = ""

'Loop through table's columns and use ColumnName to build FieldList and ValuesList
For Each c As DataColumn in table.Columns

FieldList &= c.ColumnName & ","​

ValuesList &= "@" & c.ColumnName & ","​

Next

'Strip trailing commas
FieldList = FieldList.SubString(0, FieldList.Length - 1)
ValuesList= ValuesList.SubString(0, ValuesList.Length - 1)

'Build Insert SQL string
SQLStr = "Insert Into [Products and services] (" & FieldList & ") VALUES (" & ValuesList & ")"

InsCmd = New SqlCommand(SQLStr, con)

'Now, let's get the SqlDbType and ColumnSize of each column in the table
Dim tbl As DataTable
Dim SchemaSQL As string
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
Dim iColType As Integer
Dim tColType As SqlDbType
Dim iColSize As Integer

'sql to get table schema. Note "where 1=0" makes it return no rows, just need the schema
SchemaSQL = "Select * from [Products and services] where 1=0"

cmd = New SqlCommand(SchemaSQL, con)

'get a SqlDataReader, with only the schema
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly)

'convert reader to DataTable
tbl = rdr.GetSchemaTable

'loop through each column in the table to update
For Each c As DataColumn In table.Columns

'Loop through each row in the schema table​

For Each r As DataRow In tbl.Rows​

'check if ColumnName matches​

If c.ColumnName = r.Item("ColumnName") Then​

'ColumnName matches, so get ProviderType​

iColType = r.Item("ProviderType")​

'convert ProviderType to SqlDbType​

tColType = CType(iColType, SqlDbType)​

'get ColumnSize​

iColSize = r.Item("ColumnSize")​

Exit For​

Next​

Next​

'Add parameter to SqlCommand object​

InsCmd.Parameters.Add("@" & c.ColumnName, tColType, iColSize, c.ColumnName)​

Next

'add SqlCommand to DataAdapter's InsertCommand property
sda.InsertCommand = InsCmd

'do the Update
sda.Update

You will also need to add SqlCommand objects for UpdateCommand and DeleteCommand.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!