find Max value when the table is empty [Resolved]

Posted by Vanchi050585 under Sql Server on 9/23/2010 | Points: 10 | Views : 6080 | Status : [Member] | Replies : 3
hi all,

i have some issues on excute the following query:

select max(TransactionNo) from tblDailyLoanpayInfo

i want to find the maximum value of particular query(Above).

i would like to assign max value =1 when the table is empty.

pls anyone help me.

below is my coding:
--------------------------

SqlConnection con=new SqlConnection(StrCS);
SqlDataReader dr;
string query = "select Max(TransactionNo) from tblDailyLoanpayInfo";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
dr = cmd.ExecuteReader();
if (dr.Read()==true)
{
//Response.Write("availa");
Tranc =Convert.ToInt32(dr[0].ToString());
Tranc = Tranc + 1;
}
else
{
// Response.Write("not");
Tranc = 1;
}
con.Close();
txttrnsno.Text = Tranc.ToString();


Thanks

Thanks
tvn



Responses

Posted by: SheoNarayan on: 9/23/2010 [Administrator] HonoraryPlatinum | Points: 50

Up
0
Down

Resolved
Try below code.

SqlConnection con=new SqlConnection(StrCS);

string query = "select Max(TransactionNo) from tblDailyLoanpayInfo";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
object ob = cmd.ExecuteScalar();
if (ob != null)

{
//Response.Write("availa");
Tranc =Convert.ToInt32(ob.ToString());
Tranc = Tranc + 1;
}
else
{
// Response.Write("not");
Tranc = 1;
}
con.Close();
txttrnsno.Text = Tranc.ToString();


Notice the change in bolded code.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Vanchi050585, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 9/23/2010 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi Vanchi,

Small changes in your Code

string query = "select isnull(Max(TransactionNo),0) from tblDailyLoanpayInfo";

Change this line and checked it out. In isnull condition if table is empty it will return '0'.

Cheers :)

Thanks,
T.Saravanan

Vanchi050585, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mahesharyanis on: 9/23/2010 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi Vanchi,

Also you can achieve the result with the following query -

string query = "select coalesce(Max(TransactionNo),1) from tblDailyLoanpayInfo";

If the table is empty it will return '1'.

Kind Regards,
D.Mahesh

D.Mahesh

Vanchi050585, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response