Using count() in SQL Server

Question

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?

Oleg Dok · Accepted Answer · 2011-12-21 06:52:57Z

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;
}

Ishan · Accepted Answer · 2011-12-21 06:52:35Z

0

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

using the above instead of

count2 = (int)ddlistCmd_2.ExecuteScalar();

Solved the problem.

answered Dec 21, 2011 at 6:52

Ishan

4,02832 gold badges97 silver badges157 bronze badges

1 Comment

Roger Lipscombe Over a year ago

Probably because the result of ExecuteScalar is not an int. It might be a long, or a uint. The cast won't work in that case. Going via a string (don't do that) works around the problem. Find out what type is actually returned from executing your query.

Elias Hossain · Accepted Answer · 2011-12-21 07:43:46Z

0

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());

answered Dec 21, 2011 at 7:43

Elias Hossain

4,4991 gold badge23 silver badges33 bronze badges

Collectives™ on Stack Overflow

Using count() in SQL Server

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related