473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Key Required for .Update with mdb file ? Assuming True

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
Sep 15 '06 #1
2 3064
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
Miro wrote:
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
Sep 15 '06 #2
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


"Pritcham" <do******************@hotmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
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
Miro wrote:
>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

Sep 15 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: revolnip | last post by:
As attached is the code : <% Option Explicit dim lngTimer lngTimer = Timer %> <!--#include file="Connect.asp" --> <!--#include file="Settings.asp" --> <!--#include file="Common.asp" -->
3
by: Rick | last post by:
I have an interesting problem when I run the following code in Netscape (7.02) vs. IE. This page works great in IE and all my controls bring up the validation summary dialog box if the required...
4
by: René Kabis | last post by:
People, I am at my wit's end. I am using the exact code from http://aspnet.4guysfromrolla.com/articles/071002-1.aspx And yet, the code does not manage to update the database. When I go to...
4
by: antonyliu2002 | last post by:
Let me try to make clear what my concern is. I think it is a pretty interesting one, which I think of while I am developing my web application. I have an authenticated/authorized web...
5
by: jhullu | last post by:
Hi all can I update a xml node AND write the new value in a file witout re-write all the file ? sample : my xml file : <sample>
0
by: dinsdale | last post by:
Wow, this is a really embarassing thing to have to ask about... I have an asp.net page with a multiview control in it. We want to enter person info in a semi - wizard style and I wanted to use...
11
by: Naeem | last post by:
I have a Javascript function, which changes a text field of a form into a select field. Following is the function function changeStateField() { var myForm =...
7
by: Mike P | last post by:
I am trying to write my first program using threading..basically I am moving messages from an Outlook inbox and want to show the user where the process is up to without having to wait until it has...
5
by: Cubicle Intern | last post by:
Hi, I have a form with multiple fields that confirmed before the form is submitted (ex. email field needs to be completed before the form can be submitted). Once the required fields are...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.