Save ADO Recordset

[skyseh]

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

[Deepak Sakpal]

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

[skyseh]

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
****************************************************

[Deepak Sakpal]

post ur full code

[skyseh]

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

[dglienna]

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

[dglienna]

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

[Deepak Sakpal]

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

[skyseh]

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.

[Deepak Sakpal]

DataGrid1.AllowUpdate=True

you don't need anything else.

[skyseh]

Hi Deepak,

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

[Deepak Sakpal]

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

[skyseh]

Hi Deepak,

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

[Deepak Sakpal]

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

[skyseh]

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
*************************************************

[Deepak Sakpal]

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

[skyseh]

***************************************************
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?

[Deepak Sakpal]

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.