Hi,

I have 2 queries.

1st - I want a code that will generate unique number surely. This unique number will be used as a primary key in Access Database.

2nd - On a single button click(SUBMIT Button) of a web page, I have to INSERT values into 3 different Tables of Access Database. SO I have 3 INSERT Commands that will be executed from my vb.net code on a single button click.
These 3 different tables have 30 columns each.

So, i am afraid, will there be any problem. Coz I am Inserting Data in 3 tables at a time on a web page & plus the columns are also much.

Thanks for Help.

Give Unique Name to a Uploaded File in Asp.Net (C#)
visit here:
http://howinaspnet.blogspot.com

Hi there...

Access has an autonumber type of field. That will create a unique number in that column.

I don't see any problem with your second query.. that is not much data. Did you try it???

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

I agree.

1) You just need to build the primary key column as an autoincrement and you're set. This is how I create all my primary key columns by default unless exceptional requirements mandate a different table architecture.

2) I don't see this being a big deal. If you conduct performance testing and it turns out to be, than take a look at what specific areas are slowing things down, but I wouldn't look for trouble before its even built for something like this. Go for it.

__________________
-------------------------

Whatever you can do or dream you can, begin it. Boldness has genius, power and magic in it. Begin it now.
-Johann von Goethe

When Two Hearts Race... Both Win.
-Dove Chocolate Wrapper

Chroniclemaster1, Founder of www.EarthChronicle.com
A Growing History of our Planet, by our Planet, for our Planet.

Hi,

1. I cannot use Autoincrement Coz my 3 tables are related with each other. One table has Primary key & other 2 tables have foreign key.

And also I want that unique key column in a format like FG-01.
So every new record will increment this unique number, so it will become FG-02, then FG-03 & so on.

How this can be acheived.


Thanx for all Help.

Why you didn't say this in your first post??? There is no problem is the data is related on other tables.
But why in hell you want keys like that?? Key's should be easily obtained, not solve a design or a UI problem...

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

Dear friend,

Plan changed little bit, And thats why, didn't posted in 1st post.

The client now wants a specific format like FG-01, FG-02.

Let me tell u deeper. I have 3 tables for Job seekers - Personal Details, Family Details & Professional Details.

So in my backend admin panel. I have to show Latest 20 Resumes data posted in my admin home page.
So, this data is split into 3 tables and that why i need one unique field in above 3 tables which will let me retrieve data from these tables based on 1 unique key.

Thank You.

Here's what I would do:

1. Make the ID an Autonumber / identity as suggested earlier.

2. In your applicatiion, insert records in Table A (with the AutoNumber). Retrieve the new ID for the record (1, 2, 3 etc)

3. Insert records in Table B and C and link them to A using the ID you retrieved in step 2.

4. Optionally, link table B and C using the same technique (e.g. give B an AutoNumber as well if that's required).

5. Solve the ID issue in the user interface (or create a new column that stores the nnon-numeric part). For example, assuming you retrieved the ID from the database and stored it in a field called Id, you could assign a "full number" like this:

Label1.Text = string.Format("FG-{0}", Id.ToString(());

Just my 2 cents; there are many other ways to accomplish this, but this one has always worked for me.

Cheers,

Imar

__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!

Still see no problem in using just numeirc keys, and if you want also you can have this "keys".

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

I agree with Imar. Just because the client wants to see certain data in the UI does not hamstring you to implementing the database that way. Though I guess if you really wanted to you could create a counter and construct the primary key using that counter each time before you insert into the DB. I would say it's much better practice to use an autoincrement field (more stable, requires none of the work) and then use the primary key value to display the FG-{0} to display the value the client needs in the UI.

Imar also nailed the multiple tables issue. You need to insert using a stored procedure so you can take advantage of output variables. That way you can set your output parameter to scope_identity() which will return the primary key value for the newly inserted row. If tables B & C need that value, you can now specify the foreign key; if B needs the value and table C needs the primary key of field B, then use an output parameter to return the primary key of table B.

http://www.sqlteam.com/article/store...returning-data

http://www.google.com/search?q=sql+s...ient=firefox-a

__________________
-------------------------

Whatever you can do or dream you can, begin it. Boldness has genius, power and magic in it. Begin it now.
-Johann von Goethe

When Two Hearts Race... Both Win.
-Dove Chocolate Wrapper

Chroniclemaster1, Founder of www.EarthChronicle.com
A Growing History of our Planet, by our Planet, for our Planet.