Member Avatar for dragonheart

i am using VB 6.0 and MSAccess in my project.. in one of the forms i use a flexgrid to store data into and load data from database. and also to edit each cell i use a textbox.
alll these are working perfect, bt i have a problem with saving the edited data..i.e i load data from DB and then after editing i need to make changes in the DB ..

but it doesnt work,
this is the code i use load and save data..

code for save data
For a = 1 To i - 1
rs!item_code = flex.TextMatrix(a, 1)
rs!part_no = flex.TextMatrix(a, 2)
rs!item_dec = flex.TextMatrix(a, 3)
rs!qty = flex.TextMatrix(a, 4)
rs!Rate = flex.TextMatrix(a, 5)
rs!unit = flex.TextMatrix(a, 6)
rs!amt = Val(flex.TextMatrix(a, 7))
rs.Update
rs.Requery

Next a


this is the code to load data from DB


rs.MoveFirst
For a = 0 To rs.RecordCount - 1
flex.TextMatrix(a + 1, 1) = rs!item_code
flex.TextMatrix(a + 1, 2) = rs!part_no
flex.TextMatrix(a + 1, 3) = rs!item_dec
flex.TextMatrix(a + 1, 4) = rs!qty
flex.TextMatrix(a + 1, 5) = rs!Rate
flex.TextMatrix(a + 1, 6) = rs!unit
flex.TextMatrix(a + 1, 7) = rs!amt
rs.MoveNext

Next a


is there a way to edit the exizting data of DB using a flexgrid ? or do i have to write an UPDATE query and execute it ?

can someone plz help me with this ?

  • 3 Contributors
  • 3 Replies
  • 854 Views
  • 7 Years Discussion Span
  • Latest Post Latest Post by JModak

Dani AI

Generated

Short, practical checklist and fixes for saving MSFlexGrid edits back to an Access table (VB6).

Common approaches

  • Two reliable patterns: (A) keep an updateable Recordset and move it to each corresponding record before changing fields, or (B) execute an UPDATE/INSERT SQL for each grid row (parameterized commands are safest and fastest). pointed to UPDATE queries — this is usually the simplest, especially when multiple rows must be written.

Why edits often fail

  • The recordset pointer isn’t positioned to the record that matches the edited grid row.
  • Requerying inside a per-row loop resets the pointer and breaks alignment.
  • The recordset was opened read-only or with a lock/ cursor type that disallows updates.
  • AddNew was only called once when inserting multiple rows (see ’s symptom).

Practical fixes (step-by-step)

  • Give the grid a hidden column with the table primary key so each row can be identified.
  • For recordset updates: open the recordset in an updateable mode, MoveFirst (or Find the key) for each row, assign fields and call Update once per record. Do not Requery inside the loop; requery after all updates if needed.
  • For SQL updates (recommended): prepare a parameterized UPDATE (or INSERT) command once, then loop grid rows, set parameter values from the grid, and Execute for each row. This avoids quoting problems and is much faster than requerying.

Small VB6 pattern (parameterized UPDATE — prepare once, then loop)

' prepare ADODB.Command with parameter placeholders once (example)
' loop rows:
'   if row has a primary key then
'     set cmd.Parameters(...) = grid.TextMatrix(r,col)
'     cmd.Execute
'   else
'     use an INSERT command for that row
'   end if

Final tips

  • Wrap writes in a transaction (BeginTrans / CommitTrans) so partial failures can roll back.
  • Convert/validate numeric and nulls before sending to the DB.
  • For frequent in-place editing, consider a bound DataGrid so ADODB handles edits automatically.

References to posts: ’s grid/edit setup can be kept; align each grid row to a PK or use parameterized UPDATEs to ensure edits persist.

Recommended Answers

All 3 Replies

Member Avatar for choudhuryshouvi

its better to use an update query instead of using the update method.

Member Avatar for dragonheart

ya i will try that method.. thanks anyway

Member Avatar for JModak

I create a project add some item (C_Number,E Number, Model, Type, Color, Key No, Price(numeric field)) in MSFlexGrid1. then add a button when click on this add the record in data1, but only 1(one) data add in the data1 database. I use access database. How can I add all (minimum 2 or maximum 12 record) add in data1 database. Please help me.......

Private Sub Command11_Click()
Dim count As Integer
Data2.Recordset.AddNew
MSFlexGrid1.Col = 0
Data2.Recordset.Fields("C_Number") = MSFlexGrid1.Text
MSFlexGrid1.Col = 1
Data2.Recordset.Fields("E_Number") = MSFlexGrid1.Text
Data2.Recordset.Update
Data2.Refresh
End Sub

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.