How to get Max id From Database using c#?

Question

cmd = new OleDbCommand("SELECT max(substr(tr_refno,9,6))as REFID from ECHALLAN WHERE DEPT='" +tmpDept.ToString() + "' and substr(tr_refno,5,2) ='" + Tmpmonth + "'", con);                         
maxid = Convert.ToInt16( cmd.ExecuteScalar());

Error= InvalidCastException was unhandled by user code

your data types are invalid so be sure that whatever the data type for max(substr(tr_refno, 9, 6)) is matches the .NET type. also be sure to check that the value is coming back.... — Ahmed ilyas
– Ahmed ilyas, Commented Apr 22, 2014 at 10:32
What is the result of the cmd.ExecuteScalar() looks like when you debug it? And please use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. — Soner Gönül
– Soner Gönül, Commented Apr 22, 2014 at 10:44
Beware of SQL Injection! The way you just concatenate your parameter values to your query is a bar open to injections, which consists of a big security flaw. Use IDataParameter : msdn.microsoft.com/en-us/library/… — Will Marcouiller
– Will Marcouiller, Commented Apr 22, 2014 at 10:51

user3383479 · Accepted Answer · 2014-04-22 10:43:39Z

1

Before Converting it you could check the nullity:

var value = cmd.ExecuteScalar();
int maxI;
If(value !=null)
   maxId = Convert.ToInt32(value);
else 
//......................

answered Apr 22, 2014 at 10:43

user3383479

Sign up to request clarification or add additional context in comments.

Comments

Soner Gönül · Accepted Answer · 2014-04-22 12:28:07Z

0

Try this command:

cmd = new OleDbCommand("SELECT Max(COALESCE(substr(tr_refno,9,6),0)) as REFID from ECHALLAN WHERE DEPT='" +tmpDept.ToString() + "' and substr(tr_refno,5,2) ='" + Tmpmonth + "'", con);

edited Apr 22, 2014 at 12:28

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

answered Apr 22, 2014 at 10:58

leskovar

6613 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to get Max id From Database using c#?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related