Visual Basic.Net Updating Access 200 with ADO.Net 3

[MRSOLO]

Hi
I am new to Visual Basic.Net and have just started using ADO.Net to interacte with Microsoft Access 2000 databases.
Problem is I am not even getting off the ground.
Sample code that I have basically replicated from a book that modifies two existing records and adds a new record.

"daSysUserAdmin.Update(dsSysUserAdmin, "TSystemUserAdmin&quot"

Is giving the error
"An unhandled exception of type 'System.Data.OleDb.OleException' occurred in system.data.dll"

at runtime, when I check the database it has modified the existing records but has not added the new record.

I Have treble checked that my code matches the structure from the book, put in counters to understand if the record is being added in the dataset, which it is, have loaded MDAC and numerous references and tried many different things but all result in this error.
Am using Access 2000 couldnt find anything on the Internet which would suggest known problems for this version of access and ADO.Net It cant be a connection problem when the modified record is being updated. Have tried commenting out the modify records so that the program only has to concentrate on adding the new record but same error occurs.

Has anyone encountered this problem, appreciate some guidance.
Thanks

 

[VBrian]

I am also trying to make the migration from VB6 to VB.Net and can sympathize with the frustrations you are experiencing. I did come across the same problem and after much trial and error was able to actually update the Access 2000 database. Without seeing your entire code, it is difficult to determine the exact cause but here is some sample code which I have been able to get to work.

For updating a current record:
The following assumptions apply.
1) A connection has been declared and opened.
2) A DataAdapter has been declared and created and a DataSet has been filled. (DataAdapter.Fill(DataSet, "DatabaseTableName&quot
3) The connection has been closed.

Dim dt as DataTable
Dim dr as DataRow()('Whether true or not, I could only get this to work if DataRow was declared as an array "()&quot

dt = DataSet.Tables("DatabaseTableName&quot

Try
dr = dt.Select("KeyFieldName = " & Criteria)
dr(0).BeginEdit()
dr(0)("FieldName being edited&quot = "New Value"
dr(0).EndEdit()

DataAdapter.Update(DataSet,"DatabaseTableName&quot

Catch e as Exception
msgbox(e.Source, e.Message)
End Try

To add a new record:

Same assumptions as above apply.

dt = DataSet.Tables("DataBaseTableName&quot

Try
dr = dt.NewRow
dr("FieldName1&quot = "New Data"
dr("FieldName2&quot = "New Data"
(etc. until all fields have values)
dt.Rows.Add(dr)

DataAdapter.Update(DataSet, "DataBaseTableName&quot

Catch e as Exception
msgBox(e.Source, e.Message)
End Try

Please note: I too am new to VB.Net, this code worked for me, but may not be the best way to accomplish what we are trying to do here. I have found very little support from books, Microsoft KnowledgeBase or MS Help on this matter. I hope that it works for you. If there is anyone out there who has a better solution, please let me know as well.

Best of Luck,

VBrian

 

[MRSOLO]

Thanks VBrian for your input, the problem in the end was that one of my database field names was "Password" and the error was occuring when trying to insert into or modify this field, When I changed the field name to "Password1" and the necessary code it worked fine.

 

[aharris2693]

MRSOLO
Thanks for posting your own solution in this forum. I have been trying for almost 3 days to add a new record to my DB, a process that I thought should be relatively simple, and everywhere I found samples, they were just like what I already had. But like your problem, it was as simple as naming my password field something else. Again thanks for sharing this info.

 

[DougP]

me too
I was a VB6/Access guru. Lived and breathed it and could write it like writing a letter. 10 years worth of expereince.

I don't even know how to open a connection which is your first assumption.

I have a SQL connection located in the bottom is that what you mean?




DougP, MCP