Auto generation of number in .net 2010 using Ms Access

Question

0.00/5 (No votes)

See more:

I am using vb.net 2010 (Front end) & Ms Access(2010). I want to Auto generate ID when I click on ADD button. For this I used below mentioned code

VB

cmd.CommandText = "Select IsNull(Max(SRN)) from Employee"

 cmd.Connection = con

 rd = cmd.ExecuteReader(CommandBehavior.CloseConnection)

 If (rd.HasRows) Then

 rd.Read()

 str = Convert.ToInt32(rd(0))

 str = str + 1

 txtSRN.Text = str

 Else

 txtSRN.Text = "1"

 End If

I am facing 2 issues:
1. If (rd.HasRows) Then
for this statement every time system return "TRUE" whereas there is no value in Employee Table
As per the logic when there is no value in table system should execute code return in "Else" part.
2.str = Convert.ToInt32(rd(0))
"Convert.ToInt32(rd(0))" this statement every time returns "0" value i.e str=0
as per the code return above str = str + 1 i.e 0=0+1
"i.e str=0" (when I manually add 2 records in Employee table & run the program still system is giving same result.)

Posted 27-Nov-13 3:57am

Yogi ,Pune

Updated 27-Nov-13 3:59am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ron Beyer · Answer 1 · 2013-11-27T04:01:00

Solution 1

Why are you selecting IsNull(Max(SRN)) instead of just Max(SRN)?

As far as I know, IsNull returns if the value is null inside the selection. Since you have records in the database this will return false. False converted to an integer is always 0, so you are always trying to insert the value of 1.

You should be just selecting Max(SRN), removing the IsNull part.

Posted 27-Nov-13 4:01am

Ron Beyer

Comments

Yogi ,Pune 27-Nov-13 10:08am

Thanks

but what about first issue
1. If (rd.HasRows) Then
for this statement every time system return "TRUE" whereas there is no value in Employee Table
As per the logic when there is no value in table system should execute code return in "Else" part.

Ron Beyer 27-Nov-13 10:10am

Because it does always have rows! You are asking in your select statement to return one of two values, true or false (1 or 0). The IsNull part does that, so even if there are no rows in the table, IsNull will return something meaning your query will have a result every time.

phil.o 27-Nov-13 11:41am

5'd for pointing the use of IsNull function :)
Even if the global design is false, as Dave states in solution 2 : keys uniqueness should be managed from the database itself, not from the clients.

Dave Kreskowiak · Answer 2 · 2013-11-27T05:22:00

This is something you do NOT do in your code. Setup your database to autogenerate the ID's.

Why? Because of concurrency. What if two copies of your application are running and they both execute the query at the same time?? They will both generate the same ID, invalidating your ID column with duplicate entries. The database engine is setup to prevent this from happening, but only if it is generating the ID number sequence.