Data type Conversion error when running a select query with a SUM() function agaignst database.

ALTER PROCEDURE dbo.GetSupportHoursByCompanyID
@CompanyID int
      SELECT     SUM(ticket_support_time_used) AS SupportUsed
      FROM         tbl_ticket
      WHERE     (company_ID = @CompanyID)

After running the above Stored Procedure  i get the following error message from ASP.NET:

"Error converting data type nvarchar to int."

My Database code looks like this:

public List<Ticket> GetSupportTimeforCompany( int companyID)
            // Create Product collection
            List<Ticket> colTickets = new List<Ticket>();

            //Create an instance of the DataManager Class to get the connection string.
            DataManager ConnectString = new DataManager();

            // Create connection
            SqlConnection con = new SqlConnection(ConnectString.GetConnectString());

            // Create command
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetSupportHoursByCompanyID";

            //Pass the parameters sent in (companyName, companyLocation) for the SqlQuery
            cmd.Parameters.AddWithValue("@CompanyID", "companyID");

            // Execute command
            using (con)

                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                        Ticket theTicket = new Ticket();

                        theTicket.SupportTimeUsed = (decimal)reader["SupportUsed"];


                catch (Exception e)
                    throw new Exception(e.Message + " There was a problem getting the support hours used from the Database.");


            return colTickets;


Any insight would be greatly appreciated.

Thanks in Advance.

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:

            cmd.Parameters.AddWithValue("@CompanyID", "companyID");

should be:

            cmd.Parameters.AddWithValue("@CompanyID", companyID);
Do you get any error when you run the sql directly on your database ?
On which line do you get the error ?
Pratima PharandeCommented:
  // try like
        cmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = 1;   // here 1 is harcoded
        //In your case
        cmd.Parameters.AddWithValue("@CompanyID", "companyID");  //"companyID" is string , send it as integer
        cmd.Parameters.AddWithValue("@CompanyID", 1);
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

appbuilderAuthor Commented:
I get the error when i try to view the page in which the  sql query is called. I have an objectdatasouce control what populates a repeater control. The objectdatasource control ofcourse if responsible for calling the select method described above.

Also to answer your other question, i get the error message on line 146 which is where the  try catch block is.
hi, the data in the field ticket_support_time_used; what does it consists of? can you post a few values of this field?

maybe this will work

     SELECT     SUM(cast(ticket_support_time_used as float)) AS SupportUsed
      FROM         tbl_ticket
      WHERE     (company_ID = @CompanyID)


     SELECT     Cast(SUM(cast(ticket_support_time_used as float)) as float)) AS SupportUsed
      FROM         tbl_ticket
      WHERE     (company_ID = @CompanyID)

appbuilderAuthor Commented:
Great work Angellll !
Pratima PharandeCommented:
:(  ... mine also right answer.....

ok , glad to see your problem in solved
All Courses

From novice to tech pro — start learning today.