Updating SQL table from datagridview

I have an SQL query linked to a datagridview.

I can update / delete data etc using single sql statements bt when i try to do it by simply put data in the datagrid view table i get the following error.

"Update requires valid UpdateCommand when passed datarows with modified data rows"

This is what fails on the form close event.......

Imports System.Data.SqlClient

Public Class Form1
    ' Shared variables
    Dim con As SqlConnection = New SqlConnection("Server=.\SQLEXPRESS;Database=OEE;USER Id=Alan Sweeting;Trusted_Connection=True")
    Dim cmd As SqlCommand
    Dim myDA As SqlDataAdapter
    Dim myDataSet As DataSet

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

        'Binding database table to DataGridView
        cmd = New SqlCommand("Select * FROM qry_Stops", con)
        If con.State = ConnectionState.Closed Then con.Open()
        myDA = New SqlDataAdapter(cmd)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "qry_Stops")
        DataGridView1.DataSource = myDataSet.Tables("qry_Stops").DefaultView

    End Sub

Private Sub Form1_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
        'update records
        Me.Validate()
        myDA.Update(myDataSet.Tables("qry_Stops"))
        myDataSet.AcceptChanges()
        con.Close()
        con = Nothing
    End Sub
End Class

I tried seperately with a simple update line and that works fine when i know what is updated but in the case i don;t know what is going to be changed and on which row.

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        'update record
        cmd = New SqlCommand("Update qry_Stops Set [DowntimeCode]=1 Where [DowntimeDescription]='Mech'", con)
        If con.State = ConnectionState.Closed Then con.Open()
        cmd.ExecuteNonQuery()
        ShowStops() 'Rebinding to DataGridView and view result

    End Sub

Public Sub ShowStops()

        'Binding database query to DataGridView
        cmd = New SqlCommand("Select * FROM qry_Stops", con)
        If con.State = ConnectionState.Closed Then con.Open()
        myDA = New SqlDataAdapter(cmd)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "qry_Stops")
        DataGridView1.DataSource = myDataSet.Tables("qry_Stops").DefaultView

    End Sub

I am a total novice so please don't pass me to another link, i need someone to explain to me what i am doing wrong.

Thankyou
SweetingAAsked:
Who is Participating?
 
Bob LearnedConnect With a Mentor Commented:
You are creating the SqlDataAdapter dynamically in code.  The SqlDataAdapter has the 4 CRUD operation commands--DeleteCommand, InsertCommand, SelectCommand, and UpdateCommand.  In order to do those operations, you need to assign an SqlCommand to the commands that you need to execute.

If your tables have a primary key, then you can use the SqlCommandBuilder to derive those commands dynamically.

SqlCommandBuilder Example
http://www.dotnetperls.com/sqlcommandbuilder

VB.NET example:

' Make sure table is prepared. (You won't need this.)
'
Prepare()

'
' Create a DataTable with 5 columns.
'
Dim table As New DataTable()
table.Columns.Add("Weight", GetType(Integer))
table.Columns.Add("Name", GetType(String))
table.Columns.Add("Breed", GetType(String))
table.Columns.Add("Size", GetType(Char))
table.Columns.Add("Date", GetType(DateTime))

'
' Add data to the DataTable. [This will be dynamically generated from your app.]
'
AddDogRow(table, 57, "Koko", "Shar Pei")
AddDogRow(table, 130, "Fido", "Bullmastiff")
AddDogRow(table, 92, "Alex", "Anatolian Shepherd Dog")
AddDogRow(table, 25, "Charles", "Cavalier King Charles Spaniel")
AddDogRow(table, 7, "Candy", "Yorkshire Terrier")

'
' Create new SqlConnection, SqlDataAdapter, and builder.
'
Using con = New SqlConnection(ConsoleApplication1.Properties.Settings.[Default].masterConnectionString)
	Using adapter = New SqlDataAdapter("SELECT * FROM Dogs2", con)
		Using New SqlCommandBuilder(adapter)
			'
			' Fill the DataAdapter with the values in the DataTable.
			'
			adapter.Fill(table)
			'
			' Open the connection to the SQL database.
			'
			con.Open()
			'
			' Insert the data table into the SQL database.
			'
			adapter.Update(table)
		End Using
	End Using
End Using

Open in new window

0
 
SweetingAAuthor Commented:
Thankyou very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.