Specified cast is not valid when trying Convert.ToInt32(value)

Question

I am trying to run a stored procedure and for some reason it keeps telling me "Specified cast is not valid". the "hidSelectedExpenseIDs" is a hidden field that gets populated with a javascript array of id's.

Example: the "hidSelectedExpenseIDs.Value" would look like "123,124,125,126". Hence why I have the .Split(',') in there.

Here is my code:

public void hasExhistingExpenseInvoice()
{
    string[] Expenses = hidSelectedExpenseIDs.Value.Split(',');

    //check if there is an existing invoice. Then report back to the user so the
    //user knows if he/she has to check overwrite option.
    bool invoiceExists = false;

    foreach (var expense in Expenses)
    {
        var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OSCIDConnectionString"].ToString());
        var command = new SqlCommand("p_CaseFiles_Expenses_InvoiceExhists", connection);
        command.Parameters.Add(new SqlParameter("@ExpenseID", SqlDbType.Int));
        command.Parameters["@ExpenseID"].Value = Convert.ToInt32(expense);
        command.CommandType = CommandType.StoredProcedure;
        try
        {
            connection.Open();
            invoiceExists = (bool)command.ExecuteScalar();
            if (invoiceExists)
            {
                //previous invoice exhists
                Warning1.Visible = true;
                Warning1.Text = "There is an exhisting Invoice.";
            }
        }
        catch (SqlException sql)
        {
            lblStatus.Text = "Couldn't connect to the Database - Error";
            lblStatus.ForeColor = System.Drawing.Color.Red;
        }
        catch (Exception ex)//catches exception here
        {
            lblStatus.Text = "An error occured";
            lblStatus.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            if (connection.State == ConnectionState.Open)
                connection.Close();
        }
    }
}

this is my stored procedure:

ALTER PROCEDURE dbo.[InvoiceExhists]
@ExpenseID int
AS
BEGIN
    SELECT InvNumber FROM dbo.Expenses from ExpID = @ExpenseID
END

Sounds like `hidSelectedExpenseIDs` doesn't always contain what you think it contains. What's its value when this fails? — JohnnyHK, Oct 22 '12 at 14:33
debug using a break point and see what value you get for variable expense — Karthik, Oct 22 '12 at 14:34
Are you sure that your `hidSelectedExpenseIDs.Value` doesn't have a ',' at the end? — manman, Oct 22 '12 at 14:35
when i ran it through the debugger, the value is "295" as a string hence the conversion. It just confuses me a little because I have it doing the same thing on other parts of my code and it works. It must be the (bool)cmd.ExecuteScalar() like ramhound suggested — john, Oct 22 '12 at 14:36
where do you get the exception .Is it on Convert.Toint32 or execute scalar — Karthik, Oct 22 '12 at 14:37
**Tip 1:** Don't create a connection inside a loop statement. You are oppening a connection for each iteration. **Tip 2:** Use `using` statements to dispose the connection and command objects. — Ricardo Souza, Oct 22 '12 at 14:37
when it hits "invoiceExists = (bool) command.ExecuteScalar();...when reading now means I had it all wrong, it's not the Convert.toInt32 that is crashing , it's the bool conversion — john, Oct 22 '12 at 14:44

score 4 · Accepted Answer · edited May 23 '17 at 11:57

4

The logic is faulty.

Your Query returns a number, and you are trying to cast it directly to a Boolean, this can't be done in C#.

Some languages will interpret any non-zero as true, it is not the case for C# and it will throw an exception.

You will need to compare the returned value.

In this case, you should just check if there is a value, because NULL will be returned if the invoice does not exist.

This would look like this :

invoiceExists = command.ExecuteScalar() != null ;

Also I recommend reading this thread and consider using UDF instead of scalar Stored Procedures.

edited May 23 '17 at 11:57

Community

1
1

answered Oct 22 '12 at 15:33

Yan Brunet

4,727
2
25
35

Thank you for this information. I never knew you could do ExecuteScalar() != null; – john Oct 22 '12 at 17:33
1

This will evaluate and return a Boolean which will then be assigned to your invoiceExists variable. – Yan Brunet Oct 22 '12 at 17:37

score 2 · Answer 2 · answered Oct 22 '12 at 14:46

2

change your stored procedure .This fits your requirement

ALTER PROCEDURE [dbo].[InvoiceExhists]
@ExpenseID int
AS
BEGIN
if exists(select * Expenses where ExpID = @ExpenseID)
select 1
else
select 0
END

answered Oct 22 '12 at 14:46

Karthik

2,391
6
34
65

for some reason this code still returns a 1 even if there is no result – john Oct 22 '12 at 15:14
1

@john did you add the missing 'from' to the select – Karthik Oct 22 '12 at 17:35

score 1 · Answer 3 · answered Oct 22 '12 at 14:33

1

The exception is likely caused by invoiceExists = (bool)command.ExecuteScalar(); considering its the only casting that happens within the try statement. You need to look at the return result of ExecuteScalar() to solve your problem.

answered Oct 22 '12 at 14:33

Security Hound

2,577
3
25
42

when I run the query in my DB, it returns "InvNumber" as title then "12345" so it is returning a value – john Oct 22 '12 at 14:38
1

@john - Neither of those can be casted to a bool. Infact they are both string values. – Security Hound Oct 22 '12 at 19:20

Specified cast is not valid when trying Convert.ToInt32(value)

3 Answers3