Newbie advice please on updating an Access database

[John Dann]

I'm in the process of learning ado.net initially to handle one
specific project. While I've got plenty of reading material to teach
me about the various ado.net classes, I'm having a little trouble
working out the best/simplest approach to updating the database and
would appreciate some guidance. Detailed constraints are as follows:

1. The database (with a single table of around 70 columns) will, for
various reasons, be an Access database and will reside on the same
single PC as will be used for viewing data and generating data
updates.

2. Data viewing and data updating will probably be best implemented as
two separate and quite independent processes. It's the updating
process where I'm struggling somewhat and this is where I'd appreciate
some advice. The new data is to be added to the database to a schedule
and is generated automatically by a PC process. It will not be exposed
to any user editing and will be validated as part of the generation
process.

3. I'd like to use native ado.net methods to implement the system -
I've never learnt ado or its predecessors and don't want to confuse my
learning of ado.net by using alternative methodology even though it
might conceivably be easier to do so.

Overall, I'm looking at the simplest way, using ado.net, of achieving
my update requirements. I can see that there are a number of options,
the problem is deciding which path to take. What I think I might want
to do is:

1. Create an empty dataset instance that maps fully, ie
field-by-field, to the complete table in my main database.

2. Populate the dataset with new data.

3. Update the database.

Step 2 is no problem. What I'm not clear about is the simplest way of
achieving step 1. Is there a single/few statement(s) I can use to do
this, ie without iterating thorugh all the fields or manually
generating a schema for the dataset (which I might have trouble
synchronising to the main database structure as the application
develops)?

TIA
JGD

 

[Cor Ligthert]

Hi John,

And it is so simple when you have the database it is typed here so watch
typos
\\\
Dim conn As New
Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
FileName)
dim myselectString as string = "Select * from mytable"
dim da as new OleDb.OledbAdapter(mySelectSrtring,conn)
dim ds as new dataset
da.fillschema(ds)
'do some stuff to fill the dataset by instance
for i as integer = 0 to 9
dim dr as datarow = ds.tables(0).newrow
for y as integer = 0 to 69
dr(y) = i.ToString & y. ToString
next
ds.tables(0).rows.add(dr)
next
dim cmb as new commandbuilder(da)
da.update(ds)
///

This fills it wiht then rows which have in every column a value depening on
the ros.

This is the most basic form of course, you have to set the fill and the
update in a try catch and end try block and when you can have concurrency or
other problems make the routines for that.

I hope this helps?

Cor

 

[Cor Ligthert]

To much typos

This fills it wiht then rows which have in every column a value depening on
the ros.

This fills it with ten rows that have in every column a value depending on
that row.

Cor

 

[John Dann]

On Sat, 17 Jul 2004 12:42:05 +0200, "Cor Ligthert"

Many thanks, but

da.fillschema(ds)

perhaps (?) should be:

da.fillschema(ds, schematype.mapped)

which overcomes an 'incorrect number of parameters' error, but sadly
gives an oledb.oledbException (as does schematype.source), when AFAICS
the connection strings etc are all valid. Probably I need to check
these further but there's not anything else missing is there?

Thanks
JGD

 

[John Dann]

OK, making progress - I had a typo in the Access file path.

I've also had to change

dim cmb as new commandbuilder(da)

to

dim cmb as new oledbcommandbuilder(da)

which corrects an exception. But I'm still stuck on:

da.update(ds)

which is giving me an oledbException. So the full code is now:

\\\
Dim conn As New
Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" &
FileName)
dim myselectString as string = "Select * from mytable"
dim da as new OleDb.OledbAdapter(mySelectSrtring,conn)
dim ds as new dataset
da.fillschema(ds,schematype.mapped)
..
'do some stuff to fill the dataset
..
..
dim cmb as new oledbcommandbuilder(da)
da.update(ds)
///

I can see the dataset filled OK in a datagrid if I comment out the
da.update(ds) line.

Any ideas on this final hurdle much appreciated.

JGD

 

[Cor Ligthert]

Hi John,

You are right, I was typing it and though I have to check this because that
I have not in a program.

da.fillschema(ds, schematype.mapped)

And the other one as well of course, I have really just typed in here, no
sample before my nose as I wrote, however I had the plan to check the
fillschema and than I thouht let add that filling of the datatable for you
and than I forgot it to check that scheme.

Sorry

Cor

 

[Cor Ligthert]

Yes


However you have to do it yourself I am in a hurry just answering what I can
do quick

dim cmb as new oledbcommandbuilder(da)

try
da.update(ds)
catch ex as oledb.oledbexception
messagebox.show(ex.tostring)
catch ex as exception
mesagebox.show(ex.tostring)
end try

You see than better why it is not doing what you want.

Cor

 

[John Dann]

However you have to do it yourself I am in a hurry just answering what I can
do quick

It's very kind of you to help at all so that's quite understood. The
actual error in the line

da.update(ds)

reports as:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO
statement.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)

This is presumably some internal error in running the commandbuilder
because I have no explicit 'INSERT INTO' statement in my code.

JGD

 

[Cor Ligthert]

Hi John,

Now you mention it, I forgot you where talking about 70 fields, it is not
impossible that extends the possibilities of the commandbuilder,

Cor

 

[John Dann]

Now you mention it, I forgot you where talking about 70 fields, it is not
impossible that extends the possibilities of the commandbuilder,

It's unlikely I think unless the limit is really quite small. Although
I need to use close on 70 fields in the full database as per my
original post, I'm just testing a prototype database with 13 fields.

JGD

 

[Cor Ligthert]

Hi John,

I tested it completly and this test did run, try it, just open a project,
delete all the form code, set in your projectproperties the start to sub
main, and add a reference as told in the sample from Com.

It did give exactly what I expected 71 columns filed as I wrote before.

I hope this helps?

Cor

\\\
Module Main
'Set a reference to Microsoft ADO Ext 2.7 for bla bla
Public Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fd As New SaveFileDialog
fd.FileName = "John.mdb"
fd.Filter = "MS Access|*.mdb"
If fd.ShowDialog = DialogResult.OK Then
Dim fi As New IO.FileInfo(fd.FileName)
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
fd.FileName)
Dim conn As New Data.OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fd.FileName)
conn.Open()
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE MyTable ( " & _
"AutoId int identity ," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ",
conn)
doCmd(cmd)
For i As Integer = 0 To 69
cmd = New OleDb.OleDbCommand("ALTER TABLE MyTable " & _
"ADD " & i.ToString & " int", conn)
doCmd(cmd)
Next
conn.Close()
Dim myselectString As String = "Select * from MyTable"
Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter(myselectString, conn)
da.FillSchema(ds, SchemaType.Source)
For i As Integer = 0 To 9
Dim dr As DataRow = ds.Tables(0).NewRow
For y As Integer = 1 To 70
dr(y) = i.ToString & y.ToString
Next
ds.Tables(0).Rows.Add(dr)
Next
Dim cmb As New OleDb.OleDbCommandBuilder(da)
da.Update(ds)
End Sub
Private Sub doCmd(ByVal cmd As Data.OleDb.OleDbCommand)
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
End Sub
End Module
///

 

[John Dann]

Yes - many thanks for that. I can get your code to run OK.

Now I need to figure out why my code, which is seemingly identical in
how it creates the dataset and tries to update the database throws
the INSERT error. The main difference is that I'm trying to access a
pre-existing database so maybe I should first look at the properties
of that database.

JGD

 

[John Dann]

Well, I haven't been able to detect any reason that my database won't
accept the update unless there's some aspect of it that is not fully
compatible with the JET 4.0 engine. (It was created in Access 2000).
It seems odd that the schema is read OK but the update fails.

Anyway, it seems like I need to create my database in code as per
Cor's example, which I can get to run fine. But one detail I'm unclear
about is the syntax to use for defining the field type when adding new
columns. In Cor's example the line

cmd = New OleDb.OleDbCommand("ALTER TABLE MyTable " & _
"ADD " & i.ToString & " int", conn)

seems to both define the field type and to add a value for each field
in the first row at the same time. But I how would I define the field
type to create an empty database without adding any values? For
example a couple of the field types I need to use would be DateTime
and Int16.

JGD

 

[Cor Ligthert]

Hi John,

cmd = New OleDb.OleDbCommand("ALTER TABLE MyTable " & _
"ADD " & i.ToString & " int", conn)

No I do not add a value, the columnnames are 0 to 69 just a little bit lazy
could have been nicer, however for this sample right.

The filling of it you see in the for y and for i loop.

What is written is
cmd = New OleDb.OleDbCommand("ALTER TABLE MyTable " & _

"ADD 0 int, conn)

However you can skip this part and than set in the create part
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE MyTable ( " & _
"AutoId int identity ," & _
"Mydate1 DateTime," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ",
conn)
doCmd(cmd)

Again typed in this message so watch typos.

Cor

 

[Paul Clement]

¤ I'm in the process of learning ado.net initially to handle one
¤ specific project. While I've got plenty of reading material to teach
¤ me about the various ado.net classes, I'm having a little trouble
¤ working out the best/simplest approach to updating the database and
¤ would appreciate some guidance. Detailed constraints are as follows:
¤
¤ 1. The database (with a single table of around 70 columns) will, for
¤ various reasons, be an Access database and will reside on the same
¤ single PC as will be used for viewing data and generating data
¤ updates.
¤
¤ 2. Data viewing and data updating will probably be best implemented as
¤ two separate and quite independent processes. It's the updating
¤ process where I'm struggling somewhat and this is where I'd appreciate
¤ some advice. The new data is to be added to the database to a schedule
¤ and is generated automatically by a PC process. It will not be exposed
¤ to any user editing and will be validated as part of the generation
¤ process.
¤
¤ 3. I'd like to use native ado.net methods to implement the system -
¤ I've never learnt ado or its predecessors and don't want to confuse my
¤ learning of ado.net by using alternative methodology even though it
¤ might conceivably be easier to do so.
¤
¤ Overall, I'm looking at the simplest way, using ado.net, of achieving
¤ my update requirements. I can see that there are a number of options,
¤ the problem is deciding which path to take. What I think I might want
¤ to do is:
¤
¤ 1. Create an empty dataset instance that maps fully, ie
¤ field-by-field, to the complete table in my main database.
¤
¤ 2. Populate the dataset with new data.
¤
¤ 3. Update the database.
¤
¤ Step 2 is no problem. What I'm not clear about is the simplest way of
¤ achieving step 1. Is there a single/few statement(s) I can use to do
¤ this, ie without iterating thorugh all the fields or manually
¤ generating a schema for the dataset (which I might have trouble
¤ synchronising to the main database structure as the application
¤ develops)?

I would simply connect to the Access database and execute a SELECT query that returns no rows using
the DataAdapter. This will provide you with an empty DataTable in a DataSet (with all of the
columns) that you can add rows to and then update.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)