?
Solved

how to update the table  with the datagridvalues using ADODB.recordset

Posted on 2004-10-22
23
Medium Priority
?
285 Views
Last Modified: 2010-05-02
I would like to know how do i update the values from the data grid to the database table. after making changes in the datagrid. i would loike to load the datagrid with database table values and after making changes i would like to update the values in the database.
0
Comment
Question by:raj_ms
  • 13
  • 4
  • 4
  • +1
22 Comments
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12379078
Its just a matter of setting some properties in datagrid. like allowupdate. once you make changes to the grid and change the row. the changes will be reflected in the database. AllowAddnew will allow the user to create a new record by filling up the empty row in the bottom. and do on. :)
0
 

Author Comment

by:raj_ms
ID: 12379086
NO i am not using ADODC
0
 

Author Comment

by:raj_ms
ID: 12379088
What you say is, when we use ADO DC control
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:raj_ms
ID: 12379101
I have enabled the checkbox in the datagrid, even now its not updating becaz i am not using ADO Data control
0
 

Author Comment

by:raj_ms
ID: 12379104
i want to use without ADO DC control
0
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12379107
did you checked the allowupdate property?
0
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12379113
that should work. even in adodb.

datagrid.datasource = rs  'something like this right? after creating recordset

0
 

Author Comment

by:raj_ms
ID: 12379122
yes i have checked but what you say is to fill datagrid wiyth rs but i want to update the changes in the datagrid to database table
0
 

Author Comment

by:raj_ms
ID: 12379126
i want the vice versa
0
 

Author Comment

by:raj_ms
ID: 12379131
Dear Noel_Castillo,
I have done what ever you have told me. but i wish to update the table with the datagrid
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 12379384
Add a datagrid control and a button on a form.When the form is loaded try changing the value of a cell and then click update.Then check your original database, and you'll see it is updated.


Dim mycnn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub cmdUpdate_Click()
rs.UpdateBatch
End Sub

Private Sub Form_Load()
Set mycnn = New ADODB.Connection
Set rs = New ADODB.Recordset
mycnn.ConnectionString = "Provider=MSDataShape;Data Provider=SQLOLEDB;Server=FIT4;Database=Host;UID=sa;PWD=;"
mycnn.Open
rs.Open "Add your SQL statement here", mycnn, adOpenDynamic, adLockBatchOptimistic

Set DataGrid1.DataSource = rs

End Sub
0
 

Author Comment

by:raj_ms
ID: 12379424
No it didnt update
0
 

Author Comment

by:raj_ms
ID: 12379476
when i see the table its not updated
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 12379479
it did mine.
Did any error come up?
did you set the connection string correct?
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 12379583
it is strange because the code i gave you works with me.

Have you checked if your database allows updates?

Do you have administrative rights on the pc you're using?


After you press the update button on your form,have hou refreshed your database so to be sure you have the updated values?
0
 

Author Comment

by:raj_ms
ID: 12379648

Public Sub loadRates()
   Set lr = New ADODB.Recordset
   lr.CursorLocation = adUseClient
   lr.Open "select * from raj.rates", cn, 3, 2
   Set Form1.DataGrid2.DataSource = lr
   Form1.DataGrid2.Columns(0).Visible = False
End Sub
Public Sub clearRates()
    Set Form1.DataGrid2.DataSource = Nothing
End Sub
Sub changeRates()
lr.updateBatch
End Sub
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 1500 total points
ID: 12379673
try this:


Public Sub loadRates()
   Set lr = New ADODB.Recordset
   lr.CursorLocation = adUseClient
   lr.Open "select * from raj.rates", cn, adOpenDynamic, adLockBatchOptimistic     '<-----The change is here
   Set Form1.DataGrid2.DataSource = lr
   Form1.DataGrid2.Columns(0).Visible = False
End Sub
Public Sub clearRates()
    Set Form1.DataGrid2.DataSource = Nothing
End Sub
Sub changeRates()
lr.UpdateBatch
End Sub
0
 

Author Comment

by:raj_ms
ID: 12379737
it produces an error
Row cannot be located for updating. Somevalues may have been changed since it was last read.
0
 
LVL 1

Expert Comment

by:bangerarun
ID: 12387301

Dear
its so simple

Only to set the datagrid allowupdate property to true
and what ever changes you are entering in your gird are automatically update in your database.

You havenothing to do it except only to make chanege the
allowautoupdate propertly to true

Banger
0
 

Author Comment

by:raj_ms
ID: 12393812
i have set the allowupdate property to true.......
even now i couldnt update.........
0
 
LVL 3

Expert Comment

by:Noel_Castillo
ID: 12396470
im sorry i have to go last friday before answering ur last query. Im wondering why that doesnt work for you. Are you sure that the table is updatable? Or you have a full access on it? :)
0
 

Author Comment

by:raj_ms
ID: 12398553
s indeed i tried it manually
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question