Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-22
23
Medium Priority
?
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 4
  • 4
  • +1
23 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

610 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