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

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

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)
            {
                try
                {
                    con.Open();

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


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


                        colTickets.Add(theTicket);
                    }




                    reader.Close();
                    con.Close();
                }
                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.

appbuilderAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

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

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

should be:


            cmd.Parameters.AddWithValue("@CompanyID", companyID);
0
 
DhaestCommented:
Do you get any error when you run the sql directly on your database ?
On which line do you get the error ?
0
 
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);
0
Never miss a deadline with monday.com

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.
0
 
LimbeckCommented:
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)

or

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


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

ok , glad to see your problem in solved
0
All Courses

From novice to tech pro — start learning today.