Auto generation of ID 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

C#

cmd.CommandText = "Select 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

system gives me casting error for "str = Convert.ToInt32(rd(0))" statement.
Error:Object cannot be cast from DBNull to other types.

When I use "Convert.ToString(rd(0))" then system throws an error: Conversion from string "" to type 'Double' is not valid. for "str = str + 1" statement

Posted 27-Nov-13 17:52pm

Yogi ,Pune

Updated 27-Nov-13 17:56pm

Tom Marvolo Riddle

v2

Add a Solution

1 solution

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)

Tom Marvolo Riddle · Answer 1 · 2013-11-27T18:27:00

Solution 1

Select Max(SRN) from Employee

This query returns NULL when SRN has no values

Then it satisfies the condition (rd.hasrows) because it has single record as NULL.
When you trying to convert the null value it shows the error Object cannot be cast from DBNull to other typesbecause you cannot convert NULL value into anything .

Posted 27-Nov-13 18:27pm

Tom Marvolo Riddle

Comments

Yogi ,Pune 28-Nov-13 0:30am

then what should be the code for the same? Please give me.

Tom Marvolo Riddle 28-Nov-13 0:55am

Try this:
Use ISNULL in sql

Select ISNULL(Max(SRN),0) from Employee

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

cmd.Connection = con

rd = cmd.ExecuteReader(CommandBehavior.CloseConnection)

rd.Read()

If (rd.HasRows) Then

str = Convert.ToInt32(rd(0))

str = str + 1

txtSRN.Text = str

Else

txtSRN.Text = "1"

End If

Yogi ,Pune 28-Nov-13 1:05am

Still system throws an error Object cannot be cast from DBNull to other types.
for "str = Convert.ToInt32(rd(0))" statement

Tom Marvolo Riddle 28-Nov-13 1:06am

and this:
cmd.CommandText = "Select Max(SRN) from Employee"

cmd.Connection = con

rd = cmd.ExecuteReader(CommandBehavior.CloseConnection)

rd.Read()

If (rd.HasRows) Then

If (dr(0) <> DBNull.Value) Then

str = Convert.ToInt32(rd(0))

str = str + 1

txtSRN.Text = str

End If

Else

txtSRN.Text = "1"

End If

I don't know vb.net.i just tried it

Tom Marvolo Riddle 28-Nov-13 1:08am

Try it and let me know

Yogi ,Pune 28-Nov-13 3:00am

now system gives error to "If (rd(0) <> DBNull.Value) Then" statement
Operator '<>' is not defined for types 'Object' and 'System.DBNull'

Tom Marvolo Riddle 28-Nov-13 4:18am

post your code here

Yogi ,Pune 28-Nov-13 4:39am

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Collections

Dim con As New OleDbConnection
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\HR.accdb;"
con.Open()
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim cmd As New OleDbCommand
Dim rd As OleDbDataReader
Dim str As String
cmd.Connection = con
cmd.CommandText = "Select Max(SRN) from Employee"

rd = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If (rd.HasRows = False) Then

txtSRN.Text = 1
Else
rd.Read()
Str = Convert.ToInt32(rd(0))
str = str + 1
txtSRN.Text = str
End If
End Sub

Tom Marvolo Riddle 28-Nov-13 5:05am

Str =rd(0)
if()//check str not equal to "null" here
str = str + 1
txtSRN.Text = str
End If
End Sub