Conversion failed during calling stored procedure

Question

Here is my stored procedure:

[dbo].[DFW_Completed_Safety] (
    @StartDate VARCHAR(10),
    @Station VARCHAR(50),
    @EmployeeID INT)

When I code the following:

SqlDataAdapter daAC_CSM = new SqlDataAdapter();
DataSet dsAC_CSM = new DataSet();
try
{
    using (SqlConnection sqlConnection = new SqlConnection(connectionString))
    {
        sqlCmd = new SqlCommand();
        sqlCmd.CommandType = CommandType.StoredProcedure;               
        sqlCmd.Connection = sqlConnection;
        sqlCmd.CommandTimeout = 0;
        sqlCmd.CommandText = "DFW_Completed_Safety";
        sqlCmd.Parameters.AddWithValue("@StartDate", startdate);
        sqlCmd.Parameters.AddWithValue("@Station", station);
        sqlCmd.Parameters.AddWithValue("@EmployeeID", "0");
        daAC_CSM.SelectCommand = sqlCmd;
        daAC_CSM.Fill(dsAC_CSM);
    }
    return dsAC_CSM;
}
catch (Exception)
{
    throw;
}

it throws the Exception: EmployeeID is received as a varchar.

Conversion failed when converting the varchar value 'd ' to data type int.

Things I tried:

1- Many others post on StackOverflow suggested that Convert.ToInt32(0); would do it. Since 0 is an Int32 by default, this isn't a solution.
2- Changing the method to receive varchar (send "0") and it doesn't work too.

Thanks for any ideas! (would be greater to keep the method signature to Int).

UPDATE: The question isn't answered yet, since changing my stored procedure to varchar didn't make it.. Any ideas?

Do you need to convert your date to varchar, or change your stored proc to expect datetime data type? — Greg, Jul 22 '12 at 23:30
Greg look at my update I forgot the MAIN point of the question (EmployeeID is received as a varchar.) — Martin Gemme, Jul 22 '12 at 23:39
Sorry. Missread it before my Monday morning coffee. Have you tried calling the stored proc from SSMS to see if you get the same error? — Greg, Jul 22 '12 at 23:47
Have you tried using [SqlCommandBuilder.DeriveParameters(command)](http://msdn.microsoft.com/es-es/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters(v=vs.80).aspx) to derive the parameters instead of setting them yourself? — Luxspes, Jul 22 '12 at 23:48
Greg: Don't be sorry it's my mistake. Yes I tried it, using 0 as EmployeeID just like in C# (Execute Stored Procedure) and it works on SSMS. I don't know why it does there and not in C#.. — Martin Gemme, Jul 22 '12 at 23:50
Luxspes: What's that? I'm sorry it's the first time I see that. — Martin Gemme, Jul 22 '12 at 23:51

Luxspes · Answer 1 · 2012-07-23T00:22:16.513

0

Please rewrite your code like this:

try
{
    sqlCon = new SqlConnection(connectionString);
    sqlCmd = new SqlCommand();
    sqlCmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter daAC_CSM = new SqlDataAdapter();
    DataSet dsAC_CSM = new DataSet();
    sqlCmd.Connection = sqlCon;
    sqlCmd.CommandTimeout = 0;
    sqlCmd.CommandText = "DFW_Completed_Safety";
    sqlCmd.Parameters.AddWithValue("@StartDate", startdate);  //Using "@"
    sqlCmd.Parameters.AddWithValue("@Station", station);    //Using "@"
    sqlCmd.Parameters.AddWithValue("@EmployeeID", 0); //Using "@"

    foreach(SqlParameter p in  sqlCmd.Parameters){
      //Will print Name, Type and Value
      System.Diagnostics.Trace.WriteLine("Name:" + p.ParameterName + "Type: " + p.DbType+" Value: "+p.Value); 
    }

    sqlCon.Open();
    daAC_CSM.SelectCommand = sqlCmd;
    daAC_CSM.Fill(dsAC_CSM);
    sqlCon.Close();
    return dsAC_CSM;
}
catch (Exception ex)
{
    throw ex;
}

What does it print? What error do you get?

edited Jul 23 '12 at 00:22

answered Jul 22 '12 at 23:53

Luxspes

6,268
2
28
31

Name:StartDateType: String ; Name:StationType: String ; Name:EmployeeIDType: Int32 – Martin Gemme Jul 23 '12 at 00:02
Ok, did you try with `command.Parameters.Add("@EmployeeID", SqlDbType.Int);command.Parameters["@EmployeeID"].Value = 0;` – Luxspes Jul 23 '12 at 00:04
Also, why are you not using the "@" in your parameter names? – Luxspes Jul 23 '12 at 00:05
And, what does it print if you call the foreach WITHOUT using DeriveParameters? – Luxspes Jul 23 '12 at 00:05
Because @ in SQL Server is a variable. – Martin Gemme Jul 23 '12 at 00:06
Tell me more about foreach without using DeriveParameters? – Martin Gemme Jul 23 '12 at 00:07
If you look at the example in the MSDN documentation of [AddWithValue](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx) it uses the "@" in the parameter name – Luxspes Jul 23 '12 at 00:07
Name:@StartDateType: String ; Name:@StationType: String ; Name:@EmployeeIDType: Int32 ; Same deal. – Martin Gemme Jul 23 '12 at 00:10
Ok, now try using `sqlCmd.Parameters.AddWithValue("@EmployeeID", 0);` with @, instead of `sqlCmd.Parameters.AddWithValue("EmployeeID", 0);` – Luxspes Jul 23 '12 at 00:11
That's what I just did (e.g. Name:@StartDateType: String ; Name:@StationType: String ; Name:@EmployeeIDType: Int32 ; Same deal.) – Martin Gemme Jul 23 '12 at 00:14
Conversion failed when converting the varchar value 'a ' to data type int. (instead of 'd ', it's 'a ')... Thanks – Martin Gemme Jul 23 '12 at 00:18
That is what I currently have – Martin Gemme Jul 23 '12 at 00:24
Well, then tell what it is priting now (it should print Name, Type and Value for each parameter) – Luxspes Jul 23 '12 at 00:25
Name:@StartDateType: StringValue: 07-18-2012SqlValue: 07-18-2012 ; Name:@StationType: StringValue: XY SqlValue: XY ; Name:@EmployeeIDType: Int32Value: 0SqlValue: 0 – Martin Gemme Jul 23 '12 at 00:31
Can you please add the full exception message and stacktrace to your question? I am suspecting your problem is not in the parameter, but in the code inside your stored proc – Luxspes Jul 23 '12 at 00:33
[SqlException (0x80131904): Conversion failed when converting the varchar value 'a ' to data type int.] – Martin Gemme Jul 23 '12 at 00:36
That is not the full exception message and stacktrace, (and it is not added to the question) – Luxspes Jul 23 '12 at 00:38
Here is how a stacktrace looks like: http://stackoverflow.com/questions/51768/print-stack-trace-information-from-c-sharp – Luxspes Jul 23 '12 at 00:39
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/14256/discussion-between-user1106649-and-luxspes) – Martin Gemme Jul 23 '12 at 00:41

score 0 · Answer 2 · answered Jul 23 '12 at 15:10

When you run your procedure from SSMS you will most likely get the same error, as the error is most likely derived from the body of your procedure, rather than how you are calling it. If you have a value 'd ' in a column in the table that you're querying from - and you are comparing that column to an integer type, then you will receive that error. Also, a couple of asides:

You should be putting your SqlCommand and SqlConnection instances in a using clause or disposing of them manually since they are IDisposable.
You probably don't want throw ex in your catch block - you probably just want throw. By using throw ex you mess up the stack trace that was available in the original exception.

I did what you said, look above to look what I changed. – Martin Gemme Jul 23 '12 at 17:25 — Martin Gemme, Jul 23 '12 at 17:25

score 0 · Answer 3 · answered Jul 23 '12 at 18:24

Finally it wasn't the first line. The FormName is a field that stores the FormID. The programmer that was here before was probably a noob or changed the Column datatype to int, making all queries not to work. Thanks anyways @Matt_Whitfield & @Luxspes. By the way Luxpes, you were right, it was written line 1 even on SSMS, but I did it using the same:

EXEC    @return_value = [dbo].[DFW_Completed_Safety]
        @StartDate = N'07-18-2012',
        @Station = N'YHZ',
        @EmployeeID = 0

And by doing Print @SqlStatement, I was able to copy & paste in a new Query and see that it was the Form*Name* that was an Int. Who knew that a Name could be an Int?

Conversion failed during calling stored procedure

3 Answers3