CODEWITHSUNDEEP
c#sql-server
Flanker
Flanker

Reputation: 89

How to get Maximum of any column where date equals to somedate

I am writing a desktop application. In one Form, i have get maximum date from database table and filled in a text box (tbdate). now i have another text box (tbvouch), which will get maximum vouchernumber+1 where date is = tbdate. User can also enter the date by its own .

now if that date is present in table then it must select maximum vouchernumber +1. other wise it it gives by default voucher number as 5001.

above logic i am putting at textbox(tbvouch)'s ENTER event . My code is 

  private void tbvouch_Enter(object sender, EventArgs e)
    {

        DateTime d = Convert.ToDateTime(tbdate.Text);
        int vcnum;
        SqlConnection c = new SqlConnection();
        c.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename='D:\\Documents\\Visual Studio 2008\\Projects\\Accounts\\Accounts\\Database1.mdf';Integrated Security=True;User Instance=True";
        c.Open();
        string q = "select max(vc_number) from lgr where date = '"+d+"' ";
        SqlCommand cmd = new SqlCommand(q, c);
        SqlDataReader rd = cmd.ExecuteReader();
        try
        {
            if (rd.Read())
            {
                int val = rd.GetInt32(0);
                if (val != 0)
                {
                    vcnum = val + 1;
                    tbvouch.Text = vcnum.ToString();
                }
            }
        }
        catch (Exception ex)
        {
            tbvouch.Text = "05001";
        }
    }

Upvotes: 0

Views: 142

Answers (1)

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

  private void tbvouch_Enter(object sender, EventArgs e)
    {

        DateTime d = Convert.ToDateTime(tbdate.Text);
        int vcnum;
        SqlConnection c = new SqlConnection();
        c.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename='D:\\Documents\\Visual Studio 2008\\Projects\\Accounts\\Accounts\\Database1.mdf';Integrated Security=True;User Instance=True";
        c.Open();

        string q = "IF EXISTS(SELECT 1 FROM lgr WHERE date = @date) 
                    BEGIN 
                         select max(vc_number)+1 from lgr where date = @date 
                    END 
                    ELSE 
                    BEGIN SELECT 05001 END";
       using (var cmd = new SqlCommand(q, con))
       {
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = d;

        vcum = (int)cmd.ExecuteScalar();
        }
}

Upvotes: 2

Related Questions