CODEWITHSUNDEEP
c#asp.netsql-server
Ishan
Ishan

Reputation: 4028

Using count() in SQL Server

Here is the piece of code which I use to get the count from column nos.

//get max count of orders on server .2
    public int getmaxcountfornos(string caseno,TextBox TextBox3)
    {
        int count2 = 0;
        try
        {
            String dd_webCofig = ConfigurationManager.ConnectionStrings["counton140"].ConnectionString;
            OdbcConnection ddlistconn = new OdbcConnection(dd_webCofig);
            ddlistconn.Open();

            string cnt_2 = "select count(nos) from training_jud.orders where fil_no=@b and jdate=@c";
            OdbcCommand ddlistCmd_2 = new OdbcCommand(cnt_2, ddlistconn);

            ddlistCmd_2.Parameters.AddWithValue("b", caseno);
            ddlistCmd_2.Parameters.AddWithValue("c", Convert.ToDateTime(TextBox3.Text).ToString("yyyy-MM-dd"));

            count2 = (int)ddlistCmd_2.ExecuteScalar();
        }

        catch (Exception ee)
        {
            HttpContext.Current.Response.Write(ee.Message);
        }
        return count2;
    }

Here I am getting the exception as

Specified cast is not valid.

Can anyone help me sort out this issue?

Upvotes: 0

Views: 879

Answers (3)

Elias Hossain
Elias Hossain

Reputation: 4469

So far your code is going good, however would you please try with below code part, thanks

string cnt_2 = "select count(nos) as OrderCount from training_jud.orders where fil_no=@b and jdate=@c";
OdbcCommand ddlistCmd_2 = new OdbcCommand(cnt_2, ddlistconn);

ddlistCmd_2.Parameters.AddWithValue("@b", caseno);
ddlistCmd_2.Parameters.AddWithValue("@c", Convert.ToDateTime(TextBox3.Text).ToString("yyyy-MM-dd"));

count2 = Convert.ToInt32(ddlistCmd_2.ExecuteScalar());

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21756

Try this:

//get max count of orders on server .2
public int getmaxcountfornos(string caseno,TextBox TextBox3)
{
    int count2 = 0;
    try
    {
        String dd_webCofig = ConfigurationManager.ConnectionStrings["counton140"].ConnectionString;
        OdbcConnection ddlistconn = new OdbcConnection(dd_webCofig);
        ddlistconn.Open();

        string cnt_2 = "select count(nos) from training_jud.orders where fil_no=@b and jdate=@c";
        OdbcCommand ddlistCmd_2 = new OdbcCommand(cnt_2, ddlistconn);

        ddlistCmd_2.Parameters.AddWithValue("**@b**", caseno);
        ddlistCmd_2.Parameters.AddWithValue("**@c**", Convert.ToDateTime(TextBox3.Text).ToString("yyyy-MM-dd"));

        count2 = **Convert.ToInt32**(ddlistCmd_2.ExecuteScalar());
    }

    catch (Exception ee)
    {
        HttpContext.Current.Response.Write(ee.Message);
    }
    return count2;
}

Upvotes: 1

Ishan
Ishan

Reputation: 4028

count2 = int.Parse(ddlistCmd_2.ExecuteScalar().ToString());

using the above instead of 

count2 = (int)ddlistCmd_2.ExecuteScalar();

Solved the problem.

Upvotes: 0

Related Questions