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

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.
raj_msAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Noel_CastilloCommented:
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
raj_msAuthor Commented:
NO i am not using ADODC
0
raj_msAuthor Commented:
What you say is, when we use ADO DC control
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

raj_msAuthor Commented:
I have enabled the checkbox in the datagrid, even now its not updating becaz i am not using ADO Data control
0
raj_msAuthor Commented:
i want to use without ADO DC control
0
Noel_CastilloCommented:
did you checked the allowupdate property?
0
Noel_CastilloCommented:
that should work. even in adodb.

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

0
raj_msAuthor Commented:
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
raj_msAuthor Commented:
i want the vice versa
0
raj_msAuthor Commented:
Dear Noel_Castillo,
I have done what ever you have told me. but i wish to update the table with the datagrid
0
Ioannis ParaskevopoulosCommented:
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
raj_msAuthor Commented:
No it didnt update
0
raj_msAuthor Commented:
when i see the table its not updated
0
Ioannis ParaskevopoulosCommented:
it did mine.
Did any error come up?
did you set the connection string correct?
0
Ioannis ParaskevopoulosCommented:
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
raj_msAuthor Commented:

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
Ioannis ParaskevopoulosCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
raj_msAuthor Commented:
it produces an error
Row cannot be located for updating. Somevalues may have been changed since it was last read.
0
bangerarunCommented:

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
raj_msAuthor Commented:
i have set the allowupdate property to true.......
even now i couldnt update.........
0
Noel_CastilloCommented:
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
raj_msAuthor Commented:
s indeed i tried it manually
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.