I have a SQL stored procedure which uses openrowset command and fetches values from an excel sheet and inserts it into the database.
I have created a C# application which will call the procedure and execute it.
PROBLEM!
When I execute the procedure from SQL management studio, there are no errors. It happens perfectly. But when I execute it through the C# application I get an error: "Conversion failed when converting date and/or time from character string."
Code
SQL Query (only the insert part)
insert into tbl_item ([Item code],[Dt Created])
select[Item code] ,
case when [Dt Created] is null or [Dt Created]='' then null when ISDATE(CONVERT(nvarchar,CONVERT(datetime, [Dt Created],103))) =1 then CONVERT(datetime, [Dt Created],103) else null end as [Dt Created]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\Upload\Report.xlsx;HDR=YES;IMEX=1;',
'select * from [Sheet1$]')
C# Code
public int updateItem()
{
SqlCommand cmd; cmd = new SqlCommand("usp_updateItem", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
try
{
if (conn.State.Equals(ConnectionState.Closed))
conn.Open();
cmd.ExecuteNonQuery();
ret = Convert.ToInt32(returnParameter.Value);
}
catch (Exception e)
{
err = "Error: " + e.Message;
return -1;
}
finally
{
conn.Close();
}
return ret;
}
