Updating database with datagridview changes

[nectusss]

I have datagridview filled from database through datadapter. The problem is that I cant use generated Fill, Update and Delete commands since in my SELECT query there are two joined tables. I need some direction how to achieve this. I will like to update changes in CellEndEdit procedure. Should I fill datagridview with two SELECT statements without joined tables? Or is there some simplest way to do it?

[dunfiddlin]

Joining tables shouldn't make any difference as long as you update to exactly the same combination as you originally filled the DGV with the binding should take care of assigning the new values correctly.

[nectusss]

If I'm updating database form datagridview chagnes with da.update(dt) Im getting error

Dynamic SQL generation is not supported against multiple base table.

So why is this troubling me so much?

Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Baza_podatkovDataSet.narocilo' table. You can move, or remove it, as needed.
        Dim CONNECT_STRING As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & System.AppDomain.CurrentDomain.BaseDirectory & "baza_podatkov.mdb"
        ' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & System.AppDomain.CurrentDomain.BaseDirectory & "baza_podatkov.mdb"
       
 Dim cnn As New OleDbConnection(CONNECT_STRING)
        cnn.Open()

     
Dim sql As String = "SELECT item.item_number, item_name, order.quantity,order.notes FROM (order INNER JOIN item ON order.item_id = item.ID) WHERE(order.ID_customer = " & Form2.value & ")"

Dim cmd As New OleDbCommand(sql, cnn)

da = New OleDbDataAdapter(cmd)
      
da.Fill(ds, "item")
DataGridView1.DataSource = ds.Tables("item").DefaultView

        cnn.Close()


End Sub

[dunfiddlin]

Your problem is in trying to update single values. A Where clause is invalid in joined tables. As I said you need to make the identical selection and then update the whole lot from the dgv bound table..

[nectusss]

I see... since this is my second training app, there is so much to learn. What I've tried is:

Dim sql As String = "UPDATE item, order SET item.item_number = @item_number,   item.item_name = @item_name, order.quantity = @quantity, order.notes  = @notes FROM (order INNER JOIN item ON order.ID = item.ID)"

Dim sql As String = "SELECT item.item_number, item_name, order.quantity,order.notes FROM (order INNER JOIN item ON order.item_id = item.ID) WHERE(order.ID_customer = " & Form2.value & ")"

[nectusss]

I see... since this is my second training app, there is so much to learn. What I've tried is:

Dim sql As String = "UPDATE item, order SET item.item_number = @item_number, item.item_name = @item_name, order.quantity = @quantity, order.notes  = @notes FROM (order INNER JOIN item ON order.ID = item.ID)"

But I'm getting missing operator error...ty once more...

[dunfiddlin]

Firstly my apologies as I misled you somewhat believing that a mass update was possible when it turns out not to be. So back to the old style but with some changes which are entirely peculiar to Access databases (or rather OleDB). Example with notes ...

Public Class Form1
    Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Joiner.mdb")
    Dim ds As New DataSet ' declared here so available throughout
 
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
' all standard stuff
        Dim cmd As New OleDb.OleDbCommand("SELECT Stuff.PName, [Identity].YearJoined, Stuff.[Star Rating] FROM [Identity] INNER JOIN Stuff ON [Identity].PName=Stuff.PName", conn)
        Dim da As New OleDb.OleDbDataAdapter(cmd)
        conn.Open()
        da.Fill(ds, "Joined")
        conn.Close()
        dgv.DataSource = ds.Tables("Joined")
    End Sub
 
    Private Sub dgv_CellEndEdit(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv.CellEndEdit
 
' NB the order of sections in the SQL is different for OleDB, first is the JOIN!
 
        Dim cmd As New OleDb.OleDbCommand("UPDATE [Identity] INNER JOIN Stuff ON [Identity].PName=Stuff.PName SET [Identity].YearJoined= @YJoined, Stuff.[Star Rating]= @Stars WHERE [Identity].PName=@Pname", conn)
 
' you must declare parameters in the order in which they are met in the SQL as OleDB effectively ignores the names
' this assumes that the dgv's datasource is the table as is. Use the DGV values directly if sortable or a DataView or filter is in use.
 
        cmd.Parameters.AddWithValue("@YJoined", ds.Tables("Joined").Rows(e.RowIndex).Item(1))
        cmd.Parameters.AddWithValue("@Stars", ds.Tables("Joined").Rows(e.RowIndex).Item(2))
        cmd.Parameters.AddWithValue("@PName", ds.Tables("Joined").Rows(e.RowIndex).Item(0))
        'Dim da As New OleDb.OleDbDataAdapter(cmd)
 
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
 
    End Sub
End Class

[nectusss]

Ty mate for your solution...But sill I have one issue. If my Update query contains where on end of it, then my records are not updating and I'm 99% that my st_artikla is on 1'st position in my joined table. If i take where out of query then updating is going on but updating all items...this i understand since there is no "where" inside query.

    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Baza_podatkovDataSet.narocilo' table. You can move, or remove it, as needed.
        Dim CONNECT_STRING As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & System.AppDomain.CurrentDomain.BaseDirectory & "baza_podatkov.mdb"
        ' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & System.AppDomain.CurrentDomain.BaseDirectory & "baza_podatkov.mdb"
        Dim cnn As New OleDbConnection(CONNECT_STRING)

        cnn.Open()

          Dim sql As String = "SELECT artikel.st_artikla,artikel.naziv_artikla, narocilo.rok_izdelave FROM (narocilo INNER JOIN artikel ON narocilo.id_artkla = artikel.ID) WHERE(narocilo.ID_stranke = " & status_narocila.value & ")"
        Dim cmd As New OleDbCommand(sql, cnn)

        da = New OleDbDataAdapter(cmd)

        da.Fill(ds, "joined")

        DataGridView1.DataSource = ds.Tables("joined")
        cnn.Close()
    End Sub

    Private Sub DataGridView1_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        Dim CONNECT_STRING As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & System.AppDomain.CurrentDomain.BaseDirectory & "baza_podatkov.mdb"
        ' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & System.AppDomain.CurrentDomain.BaseDirectory & "baza_podatkov.mdb"
        Dim cnn As New OleDbConnection(CONNECT_STRING)

        cnn.Open()

        Dim sql As String = "UPDATE narocilo INNER JOIN artikel ON narocilo.id_artkla = artikel.ID SET artikel.st_artikla=@st_artikla, artikel.naziv_artikla=@naziv_artikla, narocilo.rok_izdelave=@rok_izdelave where artikel.st_artikla=@st_artikla "
        Dim cmd As New OleDbCommand(sql, cnn)

        cmd.Parameters.AddWithValue("@rok_izdelave", ds.Tables("joined").Rows(e.RowIndex).Item(2))
        cmd.Parameters.AddWithValue("@naziv_artikla", ds.Tables("joined").Rows(e.RowIndex).Item(1))
        cmd.Parameters.AddWithValue("@st_artikla", ds.Tables("joined").Rows(e.RowIndex).Item(0))

        cmd.ExecuteNonQuery()
        cnn.Close()
    End Sub
End Class

[dunfiddlin]

You didn't remember my instruction to declare the parameters in the order in which they are encountered in the sql, but more importantly you can't reuse a parameter in the same command (again this only applies to OleDB).

UPDATE narocilo INNER JOIN artikel ON narocilo.id_artkla = artikel.ID SET artikel.st_artikla=@st_artikla, artikel.naziv_artikla=@naziv_artikla, narocilo.rok_izdelave=@rok_izdelave where artikel.st_artikla=@st_artikla

Makes 4 parameters, not 3 so your Where is effectively = ""