×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Updating SQL table from a bound datagridview

Updating SQL table from a bound datagridview

Updating SQL table from a bound datagridview

(OP)
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:



CODE --> vb.net

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

RE: Updating SQL table from a bound datagridview

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

CODE

...
dgvProducts.DataSource = ds.Tables(0)
...
dgvProducts.EditMode = DataGridViewEditMode.EditOnEnter
... 


---- Andy

There is a great need for a sarcasm font.

RE: Updating SQL table from a bound datagridview

(OP)
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

RE: Updating SQL table from a bound datagridview

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

You may also investigate this post of how to update database from data grid view in vb.net


---- Andy

There is a great need for a sarcasm font.

RE: Updating SQL table from a bound datagridview


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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close