Save ADO Recordset

Hi All,

How to save or update ADO recordset made to the datagrid-datasheet(in vb)? My data is stored in Oracle database.

Thanks for helping

ado code is same for access, sql server, oracle... only the difference is connectionstring.

Thanks but I can't seem to edit and save the record made. What's wrong with my code?

************************************************
Public Db As New ADODB.Connection
Public QQry As String
Public QSql As ADODB.Recordset

Private Sub Form_Load()
Db.ConnectionString= "DSN=S;UID=user;PWD=pwd;"
Db.Open
TBRS.Open "Tb", S_GateDb, adOpenDynamic, adLockPessimistic, adCmdTable

QQry = "Select * from db "

Set QSql = New ADODB.Recordset
QSql.CursorLocation = adUseClient
QSql.Open QQry, Db, adOpenDynamic, adLockOptimistic

Set grdDataGrid.DataSource = QSql
mbDataChanged = False
End Sub


Private Sub cmdUpdate_Click()
On Error GoTo UpdateErr
QSql.UpdateBatch adAffectAll
If mbAddNewFlag Then
QSql.MoveLast 'move to the new record
End If

mbEditFlag = False
mbAddNewFlag = False
SetButtons True
mbDataChanged = False

Exit Sub
UpdateErr:
MsgBox Err.Description
End Sub
****************************************************

post ur full code

Hi
Pls have a look at the attached file. Thanks a lot!

zip up the whole project and include it (minus the forms that we don't need) The form loads with a lot of errors.

Line 284: Class MSDataGridLib.DataGrid of control grdDataGrid was not a loaded control class.
Line 201: Property Picture in cmdLast had an invalid file reference.
Line 220: Property Picture in cmdNext had an invalid file reference.
Line 239: Property Picture in cmdPrevious had an invalid file reference.
Line 258: Property Picture in cmdFirst had an invalid file reference.
Line 291: The property name _ExtentX in grdDataGrid is invalid.
Line 292: The property name _ExtentY in grdDataGrid is invalid.
Line 293: The property name _Version in grdDataGrid is invalid.
Line 294: The property name AllowUpdate in grdDataGrid is invalid.
Line 295: The property name AllowArrows in grdDataGrid is invalid.
Line 296: The property name HeadLines in grdDataGrid is invalid.
Line 297: The property name RowHeight in grdDataGrid is invalid.
Line 298: The property name AllowAddNew in grdDataGrid is invalid.
Line 307: The property name HeadFont in grdDataGrid is invalid.
Line 317: The property name Caption in grdDataGrid is invalid.
Line 318: The property name ColumnCount in grdDataGrid is invalid.
Line 331: The property name Column00 in grdDataGrid is invalid.
Line 344: The property name Column01 in grdDataGrid is invalid.
Line 345: The property name SplitCount in grdDataGrid is invalid.
Line 351: The property name Split0 in grdDataGrid is invalid.

which makes it pretty hard to debug

where and what is the error? Have you tried removing the on error statement to see what happens?

from where u want to save data ? i mean from DataGrid or from ur own ? Do u want editable datagrid ?

I am sorry but I tried to simplify the code into 1 form already to avoid confusion.
I used VB data grid to display the data from the Oracle database. There is no error but I just fail to update/add new record into the datagrid form. I wonder what's wrong. Kindly advise.

DataGrid1.AllowUpdate=True

you don't need anything else.

Hi Deepak,

By default I have allowed DataGrid1..AllowUpdate = True but that does not change anything. Any idea?

create a new project and write ur own code, don't generate it from Data Form Wizard.

Hi Deepak,

By default I have allowed DataGrid1..AllowUpdate = True but that does not change anything. Any idea?

have u created a new project ? If not, create it. I am sure there is something wrong in ur exisiting peroject.

Alright. I have recreated but not using datagrid this time.
I allow user to enter value into textbox and this value will be updated into database once they press update button.

Unfortunately I encountered error when doing debugging.
**********************************************
Run-time error
Multiple step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
***********************************************
'My code
Public DB As New ADODB.Connection
Public QQry As String
Public Recordset1 As New ADODB.Recordset

Private Sub cmdUpload_Click()
DB.ConnectionString= "DSN=S;UID=user;PWD=pwd;"
DB.Open
Recordset1.Open "Tablename", DB, adpenDynamic,adLockOptimistic, adCmdTable

Recordset1.AddNew
Recordset1("Item_no") = CStr(txtItem.Text)
Recordset1("Quantity") = CStr(txtQty.Text)
Recordset1.Update 'Error detected here!!!

...
End Sub
*************************************************

what is the data type of Item_no ? increments automatically ? if so then u don't need this :

Recordset1("Item_no") = CStr(txtItem.Text)

below code is enough :

Recordset1.AddNew
Recordset1("Quantity") = CStr(txtQty.Text)
Recordset1.Update

***************************************************
what is the data type of Item_no ? increments automatically ? if so then u don't need this :

Recordset1("Item_no") = CStr(txtItem.Text)

below code is enough :

Recordset1.AddNew
Recordset1("Quantity") = CStr(txtQty.Text)
Recordset1.Update
**************************************************

No, Item_no is not increments value. In fact I have a long list of
recordset:-

Recordset1.AddNew
Recordset1("Item_no") = CStr(txtItem.Text)
Recordset1("Quantity") = CStr(txtQty.Text)
Recordset1("Title") = CStr(txttitle.Text)
Recordset1("Price") = CStr(txtpr.Text)
Recordset1.Update

If update 1 record is ok, but I have few records which I am unable to do it. Any idea?

VB Code:

for i=1 to 10
Recordset1.AddNew
Recordset1("Item_no") = CStr(txtItem.Text)
Recordset1("Quantity") = CStr(txtQty.Text)
Recordset1("Title") = CStr(txttitle.Text)
Recordset1("Price") = CStr(txtpr.Text)
Recordset1.Update
next

Value of Item_no should be changed in loop if it is primery.