Key Required for .Update with mdb file ? Assuming True

  • Thread starter Thread starter Miro
  • Start date Start date
M

Miro

I will ask the question first then fumble thru trying to explain myself so i
dont waste too much of your time.

Question / Statement - Every mdb table needs a PrimaryKey ( or maybe an
index - i havnt tested the index yet ) so you
can use an .UPDATE( dataTable ) on the data adapter. Otherwise you will
get an exception error.
Is this statement true?

---- Now me fumbling thru
-I use to use a different language you did not need to create "keys" in the
files. Plus indexes had to be manually
maintained in alternate files. So this is a minor mdb question for
microsoft related files.

Im learning vb.net and basically I have created an mdb file with adox
The mdb file has a table and that table has 1 column and I only ever will
have 1 record
in this table.

Then at one point I update the columns in the "Version" table of the mdb
file like this:

dtVersion.Rows(0)("CurVersion") = NewVersionNo ' I can use Rows( 0 )
cause there will always be 1 record there.
daDataAdapter.Update(dtVersion) ' *** Error will be here ***


Now... I got an error the error line and the error was :
"Dynamic SQL generation for the UpdateCommand is not supported against a
SelectCommand that does not return any key column information."

So I added another field into the Table called RecID and made it a
PrimaryKey ( with adox )
- ADOXTable.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary,
"RecID")
and started the value as something silly as "01" and then the code worked.

I can even change the code like this:
dtVersion.Rows(0)("CurVersion") = NewVersionNo
'dtVersion.Rows(0)("RecID") = "XX" ' *** I can even change the primary key
value but this line is not needed and the code works.
daDataAdapter.Update(dtVersion)


So from what I can understand here, for the Update comman to work, when you
are using a Data Adapter the file needs a key.


Thank you,

Miro
 
Hi

Don't know whether this is going to help or not but the error message
you're getting implies that the method you're using to do the update
tries to create an SQL statement for you on the fly. You'd normally
write an SQL update statement along the lines of "UPDATE TableName SET
ColumnName = NewValue WHERE PrimaryKey = Whatever" so it looks like
it's trying to do something like this for you but falling where there's
no PK defined - doesn't mean you wouldn't be able to do the update
yourself though using straight SQL.

I have to ask though, if you're database will only ever have one table,
and that table will only ever have one record which only holds 1 column
of data, why on earth are you using a database at all? If you just
need to store 1 value then you've got a lot of choices about where to
store them (registry, text file, user settings, config file etc etc).

Cheers
Martin
 
Martin, the explination might be a bit long but ill try to explain it the
best way I can.
The way I came accross this error, is I have 3 books and one of the books
connects to a database file,
and shows u how to create a screen and update the data with the .update
comand. So I tried to do
the same thing "without a screen" in behind the scense.

But here is the reason:

To teach myself vb I decided I was gonna write an app that will
-install with an install shield
-has its own files that get installed with install shield.
-Then i came up with the idea to have a login with a password, and every
user can have
their own unique data ( mdb file )

-So thats when I added the ADOX so when the person logs in, and is a "new
user" that is
not connected to a database, my vbmodules are called and creates the mdb
file purely by code.

-So then I thought of, what if I create an update... fine the install shield
can run and lets say i have a
couple patches i will need to patch the data with ( for whatever reason ) -
i dunno, but wherever I have
worked in the past at least once a year you found a bug/feature that you had
to go patch the data.
So what I do is this... I created in each MDB file a table called "Version".
When the user connects to the mdb file, I get the Version from the Assembly
info ( the exe version ),
and then:
1. See if the users mdb file exists, if not create it and write the current
version into the mdb file.
2. If the users mdb file does exist, i go read the version table for the 1
record and compare the version
to the exe version.
-If the exe version is lower - i dont let them run.
-If the exe version is higher, I run all the patches / insert new fields /
indexes set up for each version
number ( in order of each version number missing that has never been done )
and each time I update
the version table to the New version.

So... To give you an example I do this in my code...
Lets say I have a Person table in my mdb file and I decide i would like to
add a "First Name" column.
-This requires an update / new exe.
So In my "Patch" module, I go to a section and basically put 1 line of code
there - ( call a sub )
Add_Fields( "cur_exe_ver", "mdbfilename", "table", "newfieldnam", "newtype",
"length" )
or i can do
Patch_Data( "cur_exe_ver", "mdbfilename" ) ' And certain patches run that
are just for the current exe version

Thats it. When the exe runs and connects to a mdb file, It finds that the
exe is "newer" than the database, so as
the user logs in for the first time it updates all the data / mdb files /
everything.

Thats why I keep the version number within the mdb file. I want the users
to take the mdb file and copy it around
and take it wherever, but by allowing that, I get to a point that different
users might end up with different exe versions.
So thats how I came up with this idea.

I guess you can look at it as everyone using a different version of
Microsoft Word but being able to share documents.
-People can share documents "up" version, and the bottom people can go
update.

So seeing that basic "framework" im trying to create, which it has been a
hellova first lesson ;) - and its almost done !!!
Here are a couple other things this does for me
( pretending this is some big app that the whole world wants and I have a
billion programmers working for the greater good )

1. Any programer who wants to add a new db file / table / field / index can
easily.
-One of the first places I worked, there were about 12 programmers and each
of us would get a "project" to
customize the software one way or another. Each of us had free reign and
could create / delete whatever any database /
field we required to get it done. ( it was a bit of a different language
and used something called .pro files ). This was possible
because every programmer before they can start working there worked support
for 1 to 1.5 years. They knew the system inside
and out.

2. An install shield isnt always necessary anymore. Really its there for
the first install, but if its a minor change or somewhat of
a minor change, you can compile the exe, and just send the exe out into the
real world. No matter where the user has the exe,
the db file will be created for them. Its all incorporated. ( takes care of
itself ).
The language actually compiled each screen as ( and the best way I can
explain this ) its own exe. So to fix a bug in 1 screen,
you go to the screen, fix it, and import it on the customers system.
Instantly there is a fix without requiring any of hte users to
log out because when they go run the screen they are running the ( new
exe ). I kinda tried to re-create that where it will
take care of itself.

3. I will always know that the db file is always conneting to a "proper
version" of the exe.
- We had users in the old company that backed up the data, but since there
was so much data they never backed up their
executable files. ( sounds funny, but these companys had a tonne of data ).
So if these companies would have a crash,
these guys would restore from backup, but then copy the exe's off their
"original" cd. Once you got to the bigger companys,
and some such as "Volvo" they were fine. But the "Ma and Pop" stores were
brutal. They didnt know anything about a computer,
and tried to save a buck here and there so tried to do everything
themselves.


Hope that makes sence.
But learning to create an app like this from start to finish is defenitly
making me "touch" every part of vb.net to
get the basic understanding to learn it. Some nights im quite proud of
myself that I get something working, other nights,
i dont. Basically go out and work 8 to 5, come home and teach myself vb
from 8 to midnight or something.


Miro
 
Back
Top