find Max value when the table is empty [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

Latest Posts