Updating Access DB from DataTable

[Leodumbi]

Hy guys

Can anyone help me on the below?

I created a small application that I'm able to add items from an access DB to a DataGridView.

Now, My questions is: suppose that I have a list of item in this DGV, containing name and Quantity. Now I would like the database to be updated with new values on Quantity column, something lke this: (Current values - Quantity on DGV).

since the data are already loaded in the Datatable (dt). How can I archieve this?

any comment will be much appreciated
Thanks

[lolafuertes]

Can you be so kind to explain wich method you used to fill the data grid view (or post the code)?

[Leodumbi]

hi there
since im too far away friom the application project, i'll try to be clear as possible

first I create a datatable

dim dt as new datatable
dim row as datarow

I then go om the form load and create colummns for this datatable.

How do I fetch the data?, well, it my not be the best way... this is how I do it:

rs = new adodb.recordset
cn = new adodb.connection
cn.open=("Provider = Microosoft.JET.OleDB.4.0; Data source= "MYDATABASE".MDB")
DIM sql AS STRING
sql=("Select * from "MYTABLE" where Mat_Descr='"& txtMatDesc.text &"'")
rs.activeconnection=cn
rs.cursortype=aduseclient
rs.cursorlocation= forward only
rs.locktype= adlockoptimistic
rs.open(sql)
if not rs.eof then

row1=field1.value ' remember this is just an exemple
row1=field1.value 
dgv.datasource=dt

more or less something like that. I promisse to put the real code once I log on with my laptop

cheers
Leo

[lolafuertes]

In the meantime, to uodate a recordset you must open it like

rs.Open "SELECT * FROM MyTable", Cn, adOpenDynamic, adLockPessimistic

Then with

rs.MoveFirst

you move to the first position in the recordset. Remember to test for the BOF and EOF contitions before you move throuh the recordset.
Then, to modify the content of a record, first locate the record. You can use the MoveFirst and MoveNext to cicle over the records.
Once found, then call the rs.Edit and assign the new values to the fields changed and update the record using the rs.Update

Hope this helps

[Leodumbi]

Hi Lola

Not working. its only subtracting the firslt record.

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        cn = New ADODB.Connection
        cn.Open("Provider = Microsoft.Jet.OleDB.4.0; Data Source=teste.mdb")

        rs = New ADODB.Recordset
        rs.Open("Select * from main", cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

        With rs
            .MoveFirst()
            If Not .EOF Then

                .Fields("Quantity").Value = Val(.Fields("Quantity").Value) - Val(linha("Quantity"))
                .Update()
            End If
        End With
    End Sub

This is how I', inserting the data to DGV

formLoad

    dt.Columns.Add(New DataColumn("Nome", GetType(String)))
    dt.Columns.Add(New DataColumn("Quantity", GetType(Double)))

Fill button

   Dim sSQL As String


        cn = New ADODB.Connection
        cn.Open("provider = Microsoft.jet.oleDb.4.0; Data Source = teste.mdb")

        rs = New ADODB.Recordset
        sSQL = ("Select * from [main] where Nome = '" & TextBox1.Text & "'")

        rs.ActiveConnection = cn
        rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rs.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
        rs.LockType = ADODB.LockTypeEnum.adLockOptimistic
        rs.Open(sSQL)
        With rs
            If Not .EOF Then
                TextBox1.Text = .Fields("Nome").Value
                'TextBox2.Text = .Fields("Quantity").Value
            Else
                MsgBox("Dados Incorectos")

                Exit Sub

            End If

        End With
        dgv.DataSource = dt
        linha = dt.NewRow

        linha("Nome") = TextBox1.Text
        linha("Quantity") = Val("1")

        dt.Rows.Add(linha)  

[lolafuertes]

Just try some thing like:

With rs
.MoveFirst()
While not .EOF
.Fields("Quantity").Value = Val(.Fields("Quantity").Value) - Val(linha("Quantity"))
.Update()
.MoveNext()
End While
End With

Hope this helps

[Leodumbi]

still just subtracting the first value of the record

[lolafuertes]

If I understood well, you have a data grid view. This DGV has Data Source dt.

dt is a DataTable you define in your Form Load event.

You then, on the fill button, open a connection to the DB. On that connection you search for records having "Nome" equals to the input in TextBox1.text

If you find any record, then you fill the TextBox1.Text with the content of the filed "Nome", that already shoul be equal to the current content of TextBox1.Text as you used it for filtering.

Then you add a new row to the DataTable (source of the Data Grid View) and fill it with the cuirrent content of TextBox1.Text for the field "Nome" and with 1 on the field "Quantity".

Then I assume someone should modify the content of the DGV and then you want to save/modify the current content of the DGV into the database.

I am right?

[Leodumbi]

that's extremelly correct

[Leodumbi]

Can Anybody Help??
Please??

[Waldek]

Hi Leodumbi,
I'm no expert but I wonder why you are using ADO and recordsets instead of ADO.net? If I were populating a datagridview from a datatable I would use a command object that used an sql string which has an extra field that multiplies your 2 existing fields within the sql string. That way it would appear straightaway when you made the datatable the dgv's datasource. The only problem then might be if the dgv needs to be able to provide insert/edit/delete functionality as well as just being able to view.

[Leodumbi]

Hi waldek

could you pleaase be more specific. see this is a learning project so I am discovering things one at a time
I would also appreciate if you directme to some usefull links.
Cheers

[Leodumbi]

guys

these is not supposed to show all data in the darabase right away. Just the choosen data. as I simulating a store software. Well.. youcome, you buy the items and I'm suposed to have my data updated so I subtract th quantity of the items you purchased from my database. I'm populating the data to datagrid through dt (datatable). Now the question would be to have tose data currently in the dt to subtract to the existing in the database. that where I'm stuck

cheers