locked
Error converting data type nvarchar to int RRS feed

  • Question

  • Hi,

     

    I get "Error converting data type nvarchar to int." It is some kind of SQL exception. This is the code in my application.

     

    SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

    SqlCommand cmd = new SqlCommand();

    Object start;

    string activityID = Session["activitygroupID"].ToString();

    cmd.Parameters.AddWithValue("@ActivityGroupID", activityID);

    cmd.CommandText = "get_startdate";

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Connection = sqlConnection1;

    sqlConnection1.Open();

    SqlDataReader reader = cmd.ExecuteReader();  //The code stops here!!!

    start = reader["StartDate"];

    sqlConnection1.Close();

     

     

    The stored procedure on the SQL-server looks like so. StartDate is of DateTime type.

     

    ALTER PROCEDURE [dbo].[get_startdate]

    -- Add the parameters for the stored procedure here

    @ActivityGroupID int

    AS

    BEGIN

    SELECT StartDate

    FROM dbo.CurrentGroup

    WHERE ActivityGroupID = @ActivityGroupID

    END

     

    I appreciate any help!

    Thursday, February 28, 2008 2:31 PM

Answers

  • The root issue is that you are passing a string from .NET to SQL.  You should really be converting that to an int in .NET.  The only reason to change your stored procedure, is if the underlying table were a varchar() column.  Otherwise you should change the .net code to do an explicit convert to Int32 in the application.

     

    Friday, February 29, 2008 9:41 AM

All replies

  • You can try changing:

     

    Code Snippet
    string activityID = Session["activitygroupID"].ToString();

     

     

    to be:

     

    Code Snippet
    int activityID = System.Convert.ToInt32(Session["activitygroupID"]);

     

     

    This will pull the exception up into your application code, where you can step into it with the debugger, and see why the activitygroupID is not an integer value.

     

    Whatever value is being passed to SQL is non-numeric, so it can not implicitly convert it to an integer.  If your activitygroupID in session is alpha-numeric and is stored as a varchar/nvarchar/char/nchar in the database, then you need to change your procedure input to match the datatype for the database table:

     

    Code Snippet

    ALTER PROCEDURE [dbo].[get_startdate]

    -- Add the parameters for the stored procedure here

    @ActivityGroupID varchar(50)

    AS

    BEGIN

    SELECT StartDate

    FROM dbo.CurrentGroup

    WHERE ActivityGroupID = @ActivityGroupID

    END

     

     

    Thursday, February 28, 2008 2:46 PM
  • Thanks for the help.

     

    When I change the parameter @ActivityGroupID in the stored procedure, I get an error reading "Error converting data type nvarchar to numeric.". The problem seems to remain. Could it be that a null value is being sent to the parameter?

     

    Friday, February 29, 2008 9:21 AM
  • The root issue is that you are passing a string from .NET to SQL.  You should really be converting that to an int in .NET.  The only reason to change your stored procedure, is if the underlying table were a varchar() column.  Otherwise you should change the .net code to do an explicit convert to Int32 in the application.

     

    Friday, February 29, 2008 9:41 AM