Updating database through Krool's VBFlexGrid

[samer22]

Hello VbForums
I'm using Sqlite3 and VbRichclient 5. In one of my forms, I am saving data by using Krool's VBFlexGrid. This is my code:

StrSql = "Select * from tblNotes Where ID =?"
       Set Rs = Cnn.OpenRecordset(StrSql)
 With Cnn.CreateSelectCommand(StrSql)
   .SetInt32 1, Text1.Text
    Set Rs = .Execute
   End With
   If Rs.RecordCount = 0 Then
       For i = 1 To VBFlexGrid1.Rows - 1
        Rs.AddNew
   Rs!ID = Text1.Text
   Rs!MyDate = Date
   Rs!Sub_Id = VBFlexGrid1.TextMatrix(i, 0)
   Rs!Note = VBFlexGrid1.TextMatrix(i, 2)  
Next i
    Rs.UpdateBatch
End If

This code is doing well.
However to update data by editing the VBFlexGrid, I 'm facing tons of problems.
I tried like this:

StrSql = "Select * from tblNotes Where ID =?"
       Set Rs = Cnn.OpenRecordset(StrSql)
 With Cnn.CreateSelectCommand(StrSql)
   .SetInt32 1, Text1.Text
    Set Rs = .Execute
   End With
   If Rs.RecordCount = 0 Then
       For i = 1 To VBFlexGrid1.Rows - 1
        Rs.AddNew
   Rs!ID = Text1.Text
   Rs!MyDate = Date
   Rs!Sub_Id = VBFlexGrid1.TextMatrix(i, 0)
   Rs!Note = VBFlexGrid1.TextMatrix(i, 2)  
Next i
    Rs.UpdateBatch
else
  For i = 1 To VBFlexGrid1.Rows - 1
   Rs!MyDate = Date
   Rs!Sub_Id = VBFlexGrid1.TextMatrix(i, 0)
   Rs!Note = VBFlexGrid1.TextMatrix(i, 2)  
Next i
    Rs.UpdateBatch
End If

but this code is updating only one field.

Then I tried to update this way

Cnn.Execute "update tblNotes set Sub_Id=" & VBFlexGrid1.TextMatrix(i, 0) & "," & _
        " Note=" & VBFlexGrid1.TextMatrix(i, 2) & "" & _
        " where ID=" & Text1.Text & ""

But without succes
thank you for any help

[Schmidt]

We need more information about potential Parent-Child relationships between:
- your tblNotes (and a potential Parent-Table, we don't know the name of currently)...
The whole thing looks like tblNotes is not a "freestanding Table without relations" (due to your ID and Sub_ID fields).

How to handle two SQLite-Tables in a Parent/Child relationship, in two editable "Krool-Grids" (via DataBinding) was recently demonstrated in this CodeBank-post:
http://www.vbforums.com/showthread.p...te-Recordsets)

The example in the above Link requires, that you initialize your DB-Connection globally at App-Startup,
(the Cnn then visible throughout your whole Project-Modules, because Publically defined in a *.bas).

If you ensure such a Public Cnn (connecting to your DB in Sub Main), then the example should be easily adaptable -
just by adjusting the Table-names in the two "Binding-Selects" of the Test-Form.

If you cannot adapt the example yourself, then at least upload your DataBase (perhaps reduced to a few Demo-Records in each Table).

HTH

Olaf

[samer22]

Thank you Olaf
Your demo is extraordinary
unfortunately I was unable to adapt it o my needs.
This my project based on your sample demo


Thank you for the help you are providing

[samer22]

It seems the way I wish to do has no way !!

Sad Day ::

[Schmidt]

It seems the way I wish to do has no way !!

There's probably dozens of ways to solve your problem, we just don't really know what the problem is.

In your Zip above, you didn't really use the DataBinding-stuff from my Demo -
and you also didn't include a DataBase with your original and complete Table-Definitions.

E.g. in your Detail-Table is a Field Stud_ID (which probably points to a Students-Table) -
but I'm not sure - since no Students-Table was in the DB you've posted.

To be able to help, we have to at least get a clue about the problem...

The minimum in your case is, that you provide at least the Table-Create-Statements of your original DB
(and perhaps a ScreenShot, to get an idea which tables will play a role on your planned Entry-Form)

To enumerate the TableDefs of an SQLite-DB (for posting it here), you can do e.g.:

  Dim Tbl As cTable
  For Each Tbl In Cnn.DataBases(1).Tables
    Debug.Print vbLf; Tbl.SQLForCreate
  Next

HTH

Olaf

[samer22]

Thanks Olaf
I felt happy when I read this

There's probably dozens of ways to solve your problem, we just don't really know what the problem is.

In your Zip above, you didn't really use the DataBinding-stuff from my Demo -
and you also didn't include a DataBase with your original and complete Table-Definitions.

I have studied your extraordinary Demo but I think it is not complying with my case.

E.g. in your Detail-Table is a Field Stud_ID (which probably points to a Students-Table) -
but I'm not sure - since no Students-Table was in the DB you've posted.

I thought the Student Table is not important in this specific situation.
What matters for me in his situation is the table of subjects and the scores table.

The minimum in your case is, that you provide at least the Table-Create-Statements of your original DB
(and perhaps a ScreenShot, to get an idea which tables will play a role on your planned Entry-Form)

I have attached another sample with complete tables.

In the attached sample, I tried to bypass the duplications by dropping the whole table and recreate it before adding new data.
But surely there is a better method.
thank you

[samer22]

please am I doing right?