C# Error: Error converting nvarchar datatype to int. HELP!

Question

Hello everyone, i'm having a really hard time with my c# application. Everytime i run the code below, it gives me an error about the convertion of nvarchar datatype to int. I have tried casting but it doesn't seem to work.

ConnectionString myConnString = new ConnectionString();
string connString = myConnString.getConnectionString();
SqlConnection connValidate = new SqlConnection(connString);

SqlCommand cmdValidate = new SqlCommand("XXX", connValidate);
cmdValidate.CommandType = CommandType.StoredProcedure;

cmdValidate.Parameters.Add(new SqlParameter("@AccountID", currentUser));
cmdValidate.Parameters.Add(new SqlParameter("@PeriodMonth", Convert.ToString(comboBoxMonth.SelectedItem).Trim().ToUpper()));
cmdValidate.Parameters.Add(new SqlParameter("@PeriodYear", Convert.ToInt32(comboBoxYear.SelectedValue)));

connValidate.Open();

Int32 result = Convert.ToInt32(cmdValidate.ExecuteScalar());

connValidate.Dispose();
connValidate.Close();

HERE IS THE STORED PROCEDURE THAT I AM USING:

ALTER PROCEDURE [dbo].[XXX]
    @AccountID char(6),
    @PeriodMonth char(10),
    @PeriodYear int,
    @Date int,
    @PeriodID int,
    @InventoryID int,
    @ProductOutID int
AS
    SELECT @PeriodID = PeriodID FROM Periods
    WHERE PeriodMonth = @PeriodMonth AND PeriodYear = @PeriodYear AND AccountID = @AccountID

    SELECT @InventoryID = InventoryID FROM Fact 
    WHERE PeriodID = @PeriodID AND AccountID = @AccountID

    SELECT @ProductOutID = ProductOutID FROM Inventory
    WHERE InventoryID = @InventoryID

    SELECT DailyOutID FROM DailyOut
    WHERE ProductOutID = @ProductOutID AND Date = @Date
RETURN

What i want to do here is to check whether a period of the same month and year entered by the user is already existing in the database. So basically, the stored procedure should return a value if the period entered already exists, if so, then a MessageBox will be shown to the user alerting about the existence of the same period. Thank you very much! :)

Answer 1

1. Start SQL Profiler so you can capture the exact sql being sent to the server.
2. Copy and paste that sql into query analyzer or SQL management studio.
3. Run it, perferably in debug mode, and tell us what line number has the problem.

My guess is that Periods.PeriodMonth isn't a char(10), but I need to see what line the error is on.

Answer 2

Try this:

Int32 result = Int32.Parse(cmdValidate.ExecuteScalar());

Answer 3

First, verify that cmdValidate.ExecuteScalar() is returning the value you think it is. I would guess the problem is in your stored procedure. If so, you might want to post a new question with different tags.

Answer 4

Try to have an out parameter like @DailyOutID

SELECT @DailyOutID =  Cast(DailyOutID As Int) FROM DailyOut 
WHERE ProductOutID = @ProductOutID AND Date = @Date

And read the parameter from your paramter collection from cmdValidate.Parameters