Adding a record to a Database Table

  • Thread starter Thread starter CES
  • Start date Start date
C

CES

All,
I've spent all day trying to find simple examples of how ADO.NET works but
I'm still out of luck. I'm trying to do one simple thing add a new row to an
Access Database. I've adapted the code below from a number of sources but
I'm getting an error on the last line "daVisitors.Update(drVisitors)". The
Compiler Error Message: BC30518: Overload resolution failed because no
accessible 'Update' can be called with these arguments. Because I don't have
a clue what I'm doing I can't see what's causing the error. The page that I
found most of this code on said it should work???

Dim cnObj as OleDbConnection = New
OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & dbPath &
";")
Dim daVisitors As OleDbDataAdapter = New OleDbDataAdapter("SELECT * From
Visitors", cnObj)
Dim dsVisitors As New DataSet()
Dim dtVisitors As DataTable
Dim drVisitors As DataRow

cnObj.Open()

daVisitors.Fill(dsVisitors, "Visitors")
dtVisitors = dsVisitors.Tables("Visitors")
drVisitors = dtVisitors.NewRow()
With drVisitors
.Item("Field1") = "Data 1"
.Item("Field2") = "Data 2"
End With
dtVisitors.Rows.Add(drVisitors)
daVisitors.Update(drVisitors)

cnObj.Close

How the hell did any of you learn this crap!!! I had an adequate knowledge
of ADO 2.5 within an hour and have had vary little problem picking up the
vb.net syntax but this makes me feel like I'm speaking a new language. Any
help and direction would be greatly appreciated.
CES
 
Is this the entire code listing? If so, then you're missing an assignment
to the UpdateCommand property of the data adapter. The update method of the
data adapter determines based on the row state of a row which action should
be performed (ie: update, insert, or delete). By declaring the data adapter
with the SQL string as the parameter to the constructor you're really
saying:

Dim daVisitors as OleDbDataAdapter = New OleDbDataAdapter()
daVisitors.SelectCommand = New OleDbCommand("SELECT * FROM Visitors", cnObj)

You'll need to define commands for the remaining database actions and assign
them like in my above example. I strongly recommend reading the MSDN
documentation on these classes.

OleDbDataAdapter:
http://msdn.microsoft.com/library/d...systemdataoledboledbdataadapterclasstopic.asp
OleDbCommand:
http://msdn.microsoft.com/library/d...rlrfsystemdataoledboledbcommandclasstopic.asp
OleDbCommandBuilder:
http://msdn.microsoft.com/library/d...rfsystemdataoledboledbcommandmemberstopic.asp

I tossed in the OleDbCommandBuilder class reference because you could
probably use it to help generate your commands.

Hope this helps,
-- Dave
 
This looks like a similar problem someone was having a couple of days ago.
Look into OleDbCommandBuilder. Chances are if you add this line:
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)

after this line:
Dim daVisitors As OleDbDataAdapter = New OleDbDataAdapter("SELECT * From
Visitors", cnObj)

your update will work fine.

The problem is that the DataAdapter doesn't automatically generate the SQL
statements needed to reconcile changes made to the DataSet so you have to
help it out a little.
 
Addison,

I tried adding the - Dim cb As OleDbCommandBuilder = New
OleDbCommandBuilder(daVisitors) but the same error occurred. The full code
is as followes:

Dim cnObj as OleDbConnection = New
OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & dbPath &
";")
Dim daVisitors As OleDbDataAdapter = New OleDbDataAdapter("SELECT * From
Visitors", cnObj)
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(daVisitors)
Dim dsVisitors As New DataSet()
Dim dtVisitors As DataTable
Dim drVisitors As DataRow

cnObj.Open()

daVisitors.Fill(dsVisitors, "Visitors")
dtVisitors = dsVisitors.Tables("Visitors")
drVisitors = dtVisitors.NewRow()
With drVisitors
.Item("UserNum") = "1256_ces"
End With
dtVisitors.Rows.Add(drVisitors)
daVisitors.Update(drVisitors)

Thanks for trying
CES
 
I read your post again and saw something that didn't strike me earlier.
You'll need to call the Update method passing in either:
1.) An array of DataRow objects or
2.) The DataTable or
3.) The DataSet
For your purposes you'll probably just want to call
daVisitors.Update(dtVisitors).

Take a look at
http://msdn.microsoft.com/library/d...emdatacommondbdataadapterclassupdatetopic.asp.
You'll see that there is no overload for passing in a single DataRow object.
 
Dave,
Thanks for the help but....

The problem with the MSDN code is that:
A) It's incomplete.
B) It assumes you know certain things.

An example is the following. I've adapted two of your links to get (see
commented out lines):

Dim myConn As New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=" & dbPath & ";")
Dim myDataAdapter As New OleDbDataAdapter()
myDataAdapter.SelectCommand = New OleDbCommand("SELECT * From Visitors",
myConn)
Dim custCB As OleDbCommandBuilder = New
OleDbCommandBuilder(MyDataAdapter)

myConn.Open()

Dim ds As DataSet = New DataSet

'In the code exapmles their are two diferent ways to .fill the
dataAdapter can you tell me what's the differance between the two. Also I'm
not sure what the "myTableName" name referes to?
'myDataAdapter.Fill(ds)
'myDataAdapter.Fill(ds, myTableName)

'Code to modify data in DataSet here ????But I'm not sure what to
add????

myDataAdapter.Update(ds, myTableName)

myConn.Close()

I know these are 101 type issues but I'm just flabbergasted that in 9 hrs. I
haven't been able to find a simple working example to update a table and the
various books I have, but have never read, are even worse for finding simple
examples.

But I do appreciate the help.
CES
 
The code in MSDN does sometimes assume you know certain things but nearly
all of what is left out is EASILY found by following some of the other links
on the page or by searching for the class in question. Take your question
regarding the different ways to populate a DataTable through the Fill()
method. Each method is an overload of the Fill() method that accepts a
different parameter list. The first example accepts only the DataSet in
which to place the DataTable, the second accepts the DataSet and a default
name. If you have questions about what a particular method does, the best
thing to do is search for the class name in MSDN and find the "members" page
and read the documentation for the method you're needing information about
(it's amazing how much you'll learn that way).

I would strongly recommend reading a tutorial about ADO.NET before trying to
write any code. As you have noticed by now, ADO.NET is an entirely
different beast than "traditional" ADO. Part of learning how to use the
framework classes involves developing a general understanding of how the
classes work. Your statements, code here, in your later post, and your
request for code samples indicate that a general tutorial is in order. The
first part of your learning will be to forget about ADO (with the exception
of the Connection, Command, and Parameter classes).

I'm going to stick to some generalities here but they should apply pretty
commonly across the OleDb and SqlClient namespaces. With ADO.NET you have
two options for working with data: connected and disconnected.

Whenever you work with classes such as DataSet, DataTable, and DataRow,
you're working with disconnected data. The data is disconnected because you
are maintaining a copy of the data in memory. Updates to the data are made
in memory and will not be commited to the database until you call an update
command through either a direct command execution or through the update
method of a data adapter.

Connected data is typically accessed through a data reader and direct
command executions.

For the remainder of this I'll focus on disconnected data.

As you've probably read, DataAdapters work in conjunction (through the
Fill() method) with DataSets to build DataTables which contain DataColumns
and DataRows. Each DataRow has an items collection that allows for
accessing each column of the table. The Update() method of the DataAdapter,
when called on a DataTable, will iterate through the rows collection,
determine what changes have occured (based on RowState) and calls the
appropriate command as defined in the DataAdapter (ie: UpdateCommand,
DeleteCommand, InsertCommand).

When you need to update an existing row, you just need to reference the
column of the row you're trying to update and assign the new value. The
documentation for RowState has good code for updating a column value.

In all honesty, ADO.NET is much too big a subject to fully understand
through just a few tutorials and code snippets. Again, I STRONGLY suggest
getting a book about ADO.NET and reading it (at least the basics of it)
before you try to do anything major with it. Without understanding how to
use the tools you'll never learn to use them properly. Until you do you'll
just be pounding screws into wood with a hammer...you can do it but it's a
lot harder than it has to be.

BTW: I used the Wrox ADO.NET book when I was just getting started with
this.
 
CES,

I answered you as well in the other thread however the message from Dave is
more than comlete in my opinion. However as a little addition to him.

There are two books always called about AdoNet in this newsgroups. That are
the books of David Sceppa from Microsoft. And from William(Bill) Vaughn,
which is again very active in this newsgroup.

Probably you can meet them tonight in a chat, I copy here a message that
Jason sand to the language.vb newsgroup.

-------------------------Message from Jason below
Wondering about how best to access data from your applications? Then bring
your questions and ask them to experts from the Visual Studio and SQL teams.
Come learn about the best approaches for working with data in your
applications..

Date:
August 3, 2004
1:00 - 2:00 P.M. Pacific time
4:00 - 5:00 P.M. Eastern time
20:00 - 21:00 GMT
(For a list of local time zones relative to GMT, please see
http://msdn.microsoft.com/chats/timezones.asp.)

Outlook Reminder:
http://msdn.microsoft.com/chats/outlook_reminders/MSDN_ADONET_Aug0304.ics
Location:
http://msdn.microsoft.com/chats (then click the name of the chat to enter
the chat room)

For more information about Visual Basic .NET, see
http://msdn.microsoft.com/vbasic/
To see a list of upcoming chats or set a reminder for this chat, see
http://msdn.microsoft.com/chats.
For archives of previous chats, see
http://msdn.microsoft.com/chats/recent.asp.

Thanks!
Jason Cooke
VB.NET Team
========
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
(c) 2004 Microsoft Corporation. All rights reserved.
 
I don't know how you could go by book examples and not get something to work
unless the books have code listing errors, which is common enough. The books
I list below have downloadable code files.

Books I used when first learning VB.NET/ADO.NET:

"Visual Basic.NET Step by Step," Michael Halvorsen, MS Press. Everything
worked. All data access is using Jet (ie., Access)

"Visual Basic.NET An Object Oriented Approach Comprehensive," by Ekedahl
and Newman, Thompsion Course Technology. Has an 80 day eval version of
VS.NET, too. You can go to http://www.Course.com and download the code for
the book. Working examples and source files.

Neither of those books covered SQL Server so I also read "VB.NET and SQL
Server 2000: Building an Effective Data Layer," by Bain, Cosnell, and Walsh,
Wrox Press. This book is really impressive, you do everything by hand
coding, so you learn a lot more than if you use drag and drop data objects.
 
Back
Top