Error Specified cast is not valid

Question

I have the below function

SqlConnection cn = new SqlConnection(constring);
cn.Open();
SqlCommand cmd = new SqlCommand("select max(ID) from EP_PATTERNS ", cn);
int h = (int)cmd.ExecuteScalar() + 1;
txtID.Text = h.ToString();
cn.Close();

How to fix this Error:

Specified cast is not valid.

please put more details about the error, it may be in another part of the code, is it a compiler error or a runtime exception — ahmedsafan86, Jul 18 '13 at 08:58
If the table is empty, the return value will be `DBNull.Value`. And this will leads to this exception. You have to check this before fallbacking to 0. — Steve B, Jul 18 '13 at 08:59
It's good to test your SQL Code in an SQL IDE first when debugging this kind of error. — Chibueze Opata, Jul 18 '13 at 09:00
What is the full message? Something like "cannot convert type XXX to type `System.Int32`". Maybe @vadz solution will work. — Jeppe Stig Nielsen, Jul 18 '13 at 09:20

score 3 · Answer 1 · answered Jul 18 '13 at 09:02

given your code I feel the easiest way to fix the logic is to edit the SQL to

select ISNULL(max(ID),0) from EP_PATTERNS

To better understand what this does you can run this SQL in SSMS:

DECLARE @table table (ID int);
SELECT MAX(ID) FROM @table;
SELECT ISNULL(MAX(ID), 0) FROM @table;

score 2 · Answer 2 · answered Jul 18 '13 at 08:58

Whether table EP_PATTERNS contains any rows? Otherwise you a trying to cast NULL to int and fails.

You code should looks like:

SqlConnection cn = new SqlConnection(constring);
cn.Open();
SqlCommand cmd = new SqlCommand("select max(ID) from EP_PATTERNS ", cn);
var value = (int?)cmd.ExecuteScalar();
int maxId = value.HasValue ? value.Value + 1 : 0;  //Increase value or default it to zero
txtID.Text = maxId.ToString();
cn.Close();

score 0 · Answer 3 · edited Dec 01 '15 at 09:36

0

Using coalesce you can achieve this:

con.Open();
cmd.Connection = con;
cmd.CommandText = "select coalesce(max(user_id),0)+1 from user_master";
int user_id = int.Parse(cmd.ExecuteScalar().ToString());
txt_user_id.Text = user_id.ToString();
con.Close();

edited Dec 01 '15 at 09:36

Arulkumar

12,966
14
47
68

answered Dec 01 '15 at 09:12

HARDIK DESAI

9
2

Error Specified cast is not valid

3 Answers3