Getting to Grips with VB.Net ADO SQL queries

[GuyCarnegie]

Hi there. I'm totally new to access programming & ADO, but am already quite a way on from what I was when I started looking into it yesterday.

I am NOT using msaccess, other than to create the accdb tables in the first place. All user interaction is via a VB.Net application.

So I have this accdb database which I've read a dataset from, and have also bound a datagrid to that dataset. Obviously I had to build a dataadapter to accomodate the connection. I did this from tutorials I found on the net.
The application I'm building is in VB.Net and will be a standalone application which can be used by mulitple users simultaneously to query the single database on a network shared area.
So for the sake of argument, lets say TWO users have the app open. Both have read the data using the DA and both have their DS populated on their DataGrid - I guess each user therefore has captured a "snapshot" of the database to work on. The highest primary key for both users might be "10002345", for example, as they have both have their own snapshot (DS) of the same database.
> User 1 adds a row to the dataset using a the "next" primary key "10002346" and "updates" the DB via the DA.
> User 2 adds a row to the their earlier snapshot of the dataset using the same primary key - generating an error - duplicate key.

So, The solution is for each user to double-check for the maximum primary key immediately before "updating" the DB. They must NOT do this from their own earlier snapshot (DS), but from the DB itself, as the DB might not be as they "photographed" it fort their DS some time earlier.

My Newbie question is:
> How do I do a quick standalone query from the database to verify the Max primary key before saving da.update(ds), without reloading the full dataset? In other words, How can I run a query SELECT MAX (PKEY) FROM table and place the result into a single integer variable (and thus into the newly created DS record) without having to repopulate my whole dataset? The PKEY is an integer, NOT an autonumber.

Do I need a second adapter - one for each SQL query? Surely not. The DA is the "channel", right, not the command itself... (I think)
Assuming I use the same adapter with a different Query, do I have to pass that to a new dataset or can I pass the result directly to an integer variable? Will any of this affect my existing dataset (earlier snapshot)?

Sorry if this is very basic question! I am completely new to this.

thanks
Guy

 

[Deleted member 147267]

I think you're asking how to calculate the next number in a number field? In the procedure that will save the record:
Dim nextNum as Long

nextNum = DMax("yourField", "yourTable") + 1

I think you will want to use Long number type, not Integer. You will run out of values just after 32,000. If you will never have that many records, then integer will work.

EDIT - If you need to use sql, you can open a recordset using something like
SELECT Max("yourfield") FROM yourTable

To be honest, working with Access concepts from VB.Net is not something I know about. Sorry if none of this helps much.

 

[GuyCarnegie]

I think you're asking how to calculate the next number in a number field? In the procedure that will save the record:
Dim nextNum as Long

nextNum = DMax("yourField", "yourTable") + 1

I think you will want to use Long number type, not Integer. You will run out of values just after 32,000. If you will never have that many records, then integer will work.

Not really Micron. I'm asking HOW to get the nextnumber from the database itself- ie how to query it. Ie how does DMax function get its info from the source database rather than from the snapshot we got from the database into the dataset earlier? Perhaps it could be 30 minutes since the dataset was populated from the source DB.

Or are you saying that the database and dataset ALWAYS REMAIN SYNCHRONISED even after the adapter connection is closed.

I'm actually using Int32, but thats a minor point I guess.

 

[cheekybuddha]

Hi,

Do you require your PK to be sequential?

An alternative method would be to ignore the PK until you first save the record. If you define it as Autonumber if will populate at that stage and you can retrieve it (your dataadapter will probably do it for you). In other words, let Access assign the next available PK and you/the user pick it up after it has been assigned.

 

[CJ_London]

I'm asking HOW to get the nextnumber from the database itself

the next number does not exist, you need to calculate it. If you don't want to use dmax then the sql would be somethin like

SELECT Max(Yourfield)+1 as nextnum from myTable

 

[Deleted member 147267]

I made edits without knowing there were other answers (usually forum notifies you?).
Anyway, the point of just letting the table do the work is a good one. If you don't need to know or control the number that will be created, then don't try to assign it. If you need to know what the number is after it is created, create another snapshot. Seems simple enough.

 

[GuyCarnegie]

the next number does not exist, you need to calculate it. If you don't want to use dmax then the sql would be somethin like

SELECT Max(Yourfield)+1 as nextnum from myTable

Yes CJ. I can work out what the SQL needs to be to calculate the nextnumber, but thanks for confirming. What I need to know is how to send that SQL query to the database to retrieve the "current" maximum number. The answer will no doubt include opening the connection and doing something with the data adapter.

 

[GuyCarnegie]

I made edits without knowing there were other answers (usually forum notifies you?).
Anyway, the point of just letting the table do the work is a good one. If you don't need to know or control the number that will be created, then don't try to assign it. If you need to know what the number is after it is created, create another snapshot. Seems simple enough.

So the only method is to fully repopulate the entire dataset via the original adapter (SELECT * from Table) which is linked to the datagrid, rather than send an additional SQL query just to return the one piece of data I need? Makes the SQL kind of pointless if I'm going to read everything in its entirety every time and then work out what I need in VB. Surely sending the SQL SELECT MAX(PKEY) from Table would be all I would need to do - I just cant work out how to send that using existing data adapters (if required) while keeping the existing dataset intact.

 

[GuyCarnegie]

Hi,

Do you require your PK to be sequential?

An alternative method would be to ignore the PK until you first save the record. If you define it as Autonumber if will populate at that stage and you can retrieve it (your dataadapter will probably do it for you). In other words, let Access assign the next available PK and you/the user pick it up after it has been assigned.

This is ALMOST exactly what I'm trying to do. I am ignoring the PKEY until the point at which I need to save the new record. Only difference is that I am not using autonumber so I need to find out what number to use.

 

[GuyCarnegie]

Hi,

Do you require your PK to be sequential?

An alternative method would be to ignore the PK until you first save the record. If you define it as Autonumber if will populate at that stage and you can retrieve it (your dataadapter will probably do it for you). In other words, let Access assign the next available PK and you/the user pick it up after it has been assigned.

Hi there. If I rebuild my table to use autonumber as the PKey, then am I right in saying that I don't have to write a number at all to that field? When I save my dataset to the DB from VB.Net via the ADO connection I just leave that field "null" in the new record???

 

[Deleted member 147267]

o the only method is to fully repopulate the entire dataset via the original adapter

I'm not saying that. I have to assume that if you know how to retrieve a full set of records that you can do the same using the sql syntax given and retrieve one value from one field from one record and add 1 to it. I cannot help you with coding to do that beyond giving an indication of the sql syntax because as I mentioned, working from the vb.net side is beyond me. As for using an autonumber as meaningful data, I (and I assume others) will caution you against that. From my view point, if you don't care what the number is, then it's not a bad idea just letting the table do the work. If you need to know the value before you use it, that's a bit more problematic. If you intend to use the value as some sort of identifier beyond just linking it to other tables as a foreign key, I would not. This is why

UtterAccess.com

www.utteraccess.com

 

[GuyCarnegie]

I'm not saying that. I have to assume that if you know how to retrieve a full set of records that you can do the same using the sql syntax given and retrieve one value from one field from one record and add 1 to it. I cannot help you with coding to do that beyond giving an indication of the sql syntax because as I mentioned, working from the vb.net side is beyond me. As for using an autonumber as meaningful data, I (and I assume others) will caution you against that. From my view point, if you don't care what the number is, then it's not a bad idea just letting the table do the work. If you need to know the value before you use it, that's a bit more problematic. If you intend to use the value as some sort of identifier beyond just linking it to other tables as a foreign key, I would not. This is why

UtterAccess.com

www.utteraccess.com

ok, thanks Micron. I know how to retrieve the full set of records in the first place, yes - using instances of the tools that VB.net gives us for working with the ADO connection. My question is whether I can reuse those same instances of those tools for a new query, or whether I need to create new instances of the tools. ie do I need two hammers to wallop two nails into two bits of wood, or can I reuse the same hammer twice? I understand that you likely won't be able to clarify that though, but thanks anyway.

 

[Deleted member 147267]

From what I know of ADO (I prefer to use DAO as I seldom needed to create remote connections and even then, preferred Automation when possible) as long as you have coded so that your connection persists, you should still be able to use it to retrieve new data. If you have closed it, then you will need to re-create it.

Perhaps if you posted your code and told us what the table and field names are, someone can work into that code a means of getting the next highest number. Probably not me as I'd have to review ADO connections and recordsets and I suspect others here have that at the tip of their fingers.

Then there is the question of why use vb.net to mine/work with Access data? Could you not develop in Access and users have free Access runtime version and utilize the events/code/sql that it supports?

 

[Minty]

Hi there. If I rebuild my table to use autonumber as the PKey, then am I right in saying that I don't have to write a number at all to that field? When I save my dataset to the DB from VB.Net via the ADO connection I just leave that field "null" in the new record???

This bit is correct - The Autonumber field cannot be written to - it will just get the next number access generates.

 

[GuyCarnegie]

From what I know of ADO (I prefer to use DAO as I seldom needed to create remote connections and even then, preferred Automation when possible) as long as you have coded so that your connection persists, you should still be able to use it to retrieve new data. If you have closed it, then you will need to re-create it.

Perhaps if you posted your code and told us what the table and field names are, someone can work into that code a means of getting the next highest number. Probably not me as I'd have to review ADO connections and recordsets and I suspect others here have that at the tip of their fingers.

Then there is the question of why use vb.net to mine/work with Access data? Could you not develop in Access and users have free Access runtime version and utilize the events/code/sql that it supports?

Corporate Environment - can't install access runtime.

 

[CJ_London]

My question is whether I can reuse those same instances of those tools for a new query, or whether I need to create new instances of the tools.

all depends on what you are trying to achieve, how many concurrent users, performance issues, level of security required, etc.

In some scenario's you open a connection when your app opens and don't close it until the app is closed. In others you open a connection, do what you need to do, then close it. Another variation is to open a connection, get a recordset, disconnect it and close the connection. User makes some changes to the disconnected recordset and on update a connection is opened again, update and append queries are executed then the connection closed. Websites will typically use the last scenario. And don't forget if you are using combos and listboxes in .net, they will also require their own recordset. You have a lot more work to do with disconnected recordsets to verify the data hasn't changed on the server since last downloaded - much the same as you would with an unbound form in access.

Surprised if your IT does not allow runtime, they will allow an access BE. My guess would be .net apps will typically use sql server/oracle or similar.

 

[GuyCarnegie]

About 10 concurrent users max.

 

[Deleted member 147267]

Surprised if your IT does not allow runtime, they will allow an access BE.

When you put it that way, it is kinda bizarre that one would not be allowed Runtime yet can have the full blown version. Even if Access is limited to a few developers, it's still in the network somewhere and there isn't much harm one can do with Runtime.

 

[GuyCarnegie]

When you put it that way, it is kinda bizarre that one would not be allowed Runtime yet can have the full blown version. Even if Access is limited to a few developers, it's still in the network somewhere and there isn't much harm one can do with Runtime.

No-one has the full version either. Excel etc, but not access. I'm developing (if you can call it that) using my computer at home.

 

[Deleted member 147267]

I am NOT using msaccess, other than to create the accdb tables in the first place.

OK, but I'm here scratching my head. I must have misunderstood what that means because I thought tables in an Access back end file were involved here. I realize you can create these at home, but you must be converting them to something else; perhaps SqlServer. Otherwise, I don't see how you can take a back end file of tables to work and upload the file to the network and have the file type recognized if it's not registered in the system. Perhaps you are doing something I've never heard of.
No matter. Hope you get a solution.