appbuilder
asked on
Data type Conversion error when running a select query with a SUM() function agaignst database.
ALTER PROCEDURE dbo.GetSupportHoursByCompa nyID
@CompanyID int
AS
SELECT SUM(ticket_support_time_us ed) 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(ConnectStrin g.GetConne ctString() );
// Create command
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedur e;
cmd.CommandText = "GetSupportHoursByCompanyI D";
//Pass the parameters sent in (companyName, companyLocation) for the SqlQuery
cmd.Parameters.AddWithValu e("@Compan yID", "companyID");
// Execute command
using (con)
{
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Ticket theTicket = new Ticket();
theTicket.SupportTimeUsed = (decimal)reader["SupportUs ed"];
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.
@CompanyID int
AS
SELECT SUM(ticket_support_time_us
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(ConnectStrin
// Create command
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedur
cmd.CommandText = "GetSupportHoursByCompanyI
//Pass the parameters sent in (companyName, companyLocation) for the SqlQuery
cmd.Parameters.AddWithValu
// Execute command
using (con)
{
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Ticket theTicket = new Ticket();
theTicket.SupportTimeUsed = (decimal)reader["SupportUs
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.
// try like
cmd.Parameters.Add("@Compa nyID", SqlDbType.Int).Value = 1; // here 1 is harcoded
//In your case
cmd.Parameters.AddWithValu e("@Compan yID", "companyID"); //"companyID" is string , send it as integer
cmd.Parameters.AddWithValu e("@Compan yID", 1);
cmd.Parameters.Add("@Compa
//In your case
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
ASKER
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.
Also to answer your other question, i get the error message on line 146 which is where the try catch block is.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_ti me_used as float)) AS SupportUsed
FROM tbl_ticket
WHERE (company_ID = @CompanyID)
or
SELECT Cast(SUM(cast(ticket_suppo rt_time_us ed as float)) as float)) AS SupportUsed
FROM tbl_ticket
WHERE (company_ID = @CompanyID)
maybe this will work
SELECT SUM(cast(ticket_support_ti
FROM tbl_ticket
WHERE (company_ID = @CompanyID)
or
SELECT Cast(SUM(cast(ticket_suppo
FROM tbl_ticket
WHERE (company_ID = @CompanyID)
ASKER
Great work Angellll !
:( ... mine also right answer.....
ok , glad to see your problem in solved
ok , glad to see your problem in solved
On which line do you get the error ?