473,703 Members | 2,615 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.U pdate(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("Primary Key", ADOX.KeyTypeEnu m.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.U pdate(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 3108
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.U pdate(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("Primary Key", ADOX.KeyTypeEnu m.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.U pdate(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_ve r", "mdbfilenam e", "table", "newfieldna m", "newtype",
"length" )
or i can do
Patch_Data( "cur_exe_ve r", "mdbfilenam e" ) ' 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.goo glegroups.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(dtVersio n) ' *** 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
SelectComman d 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("Primary Key", ADOX.KeyTypeEnu m.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.Row s(0)("RecID") = "XX" ' *** I can even change the primary
key
value but this line is not needed and the code works.
daDataAdapter. Update(dtVersio n)
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
2239
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
2786
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 field is not filled out. However in Netscape NONE of the required field validations occurs at all in Netscape. The form is posting correctly because I can walk through the post back process. Any ideas why this is happening or how to fix it?
4
1667
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 update my database, I am able to get the form fields, and I am able to replace the data, but when I go "update", the old data remains. I have done everything correct, including this:
4
1891
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 application. People have to login from http://mydomain.com/ to access the information on my site. For now, this is working fine. People cannot bypass the login form, any attempt to check out a page (if they happen to know the file name) will be...
5
5247
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
2008
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 required field validators for, well required fields. On the first view, the person enters first name, last name and a couple other things. the RFV works great on first and last name and we move on to the next view - Addresses. I want to be able to...
11
3991
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 = document.getElementsByTagName("form"); alert(myForm.stateId.value); myForm.removeChild(myForm.stateId);
7
2374
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 finished. I am trying to follow this example : http://www.codeproject.com/cs/miscctrl/progressdialog.asp But although the messages still get moved, the progress window never does anything. Here is my code in full, if anybody who knows...
5
1635
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 completed, I want a final warning box to appear asking the user "are you sure you want to make these changes?" I'm having trouble getting the function to work correctly. Here's the problem I've encountered: First Script: this one lets me enter an...
0
9236
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9094
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8988
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8946
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6581
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5918
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4415
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4673
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3108
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.