Exception cannot convert nvarchar to int

Question

I am getting the following error, even though I have checked many times I cannot fathom why would such thing pop up when I am only searching by a string, I already checked that when filling the Local object below the properties are correct.

[TestMethod]
public void TestSearchLocalName()
{
        try
        {

            IEnumerable lstLocales = gestor.searchLocal("Local1");
            Assert.IsNotNull(lstLocales);
            Console.Write("Se ha conseguido una lista de locales.");


        }
        catch (Exception ex)
        {
            Assert.Fail(ex.ToString());
        }
}

Method at gestor:

public IEnumerable<Local> searchLocal(String name)
{
        try
        {
            return LocalRepository.Instance.getByName(name);
        }
        catch (DataAccessException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new Exception(String.Format("Error: {0}", ex.Message));
        }
}

Repository:

public IEnumerable<Local> getByName(String name) {
        List<Local> lista = new List<Local>();

        try
        {
            DataTable tablaResultado = DataBaseAccess.advanceStoredProcedureRequest("pa_local_buscar_nombre", new SPP[] { 
            new SPP("loc_nombre", name.ToString())
            });

            if (tablaResultado.Rows.Count > 0)
            {

                foreach (DataRow fila in tablaResultado.Rows)
                {
                    lista.Add(new Local(

                        int.Parse(fila.ItemArray[0].ToString()),
                        fila.ItemArray[1].ToString(),
                        fila.ItemArray[2].ToString(),
                        fila.ItemArray[3].ToString(),
                        fila.ItemArray[4].ToString(),
                        int.Parse(fila.ItemArray[5].ToString()),
                        int.Parse(fila.ItemArray[6].ToString())
                    ));
                }
            }
            else
            {
                lista.Add(new Local());
            }
        }
        catch (Exception ex)
        {
            throw new Exception(String.Format("SQL Error: {0}", ex.Message));
        }

        return lista;
}

The stored procedure does work when I test it directly using the same name in he unit test, leading me to believe the error is not here but just in case:

CREATE PROCEDURE [dbo].[pa_local_buscar_nombre]
    @loc_nombre as nchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        loc_id, loc_nombre, loc_url,  loc_telefono, 
        loc_descripcion, loc_preferencia, loc_provincia  
    FROM 
        Locales
    WHERE  
        loc_nombre LIKE '%' + @loc_nombre + '%'
END

Exception:

Test Name: TestSearchLocalNombre
Test FullName: UT.UnitTestLocales.TestSearchLocalNombre
Test Outcome: Failed
Test Duration: 0:00:00.0568857
Result Message:

Assert.Fail failed. System.Exception: Error: SQL Error: Error converting data type nvarchar to int.
at BLL.GestorLocales.searchLocal(String name)

EDIT:

Advanced stored procedure:

public static DataTable advanceStoredProcedureRequest(String name, SPP[] parameters = null)
{
        String storedProcedure = "dbo." + name;

        SqlCommand cmd = new SqlCommand(name, getConection);
        cmd.CommandType = CommandType.StoredProcedure;

        if (parameters != null && parameters.Length > 0)
        {
            foreach (SPP p in parameters)
            {
                cmd.Parameters.AddWithValue("@" + p.Name, p.Value);
            }
        }

        DataTable table = new DataTable();

        SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
        dataAdapter.Fill(table);

        return table;
}

EDIT2:

SSP:

    using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class SPP
    {

        public String Name { get; set; }
        public String Value { get; set; }

        /**
         * Stored Procedure Parameter
         */
        public SPP(String name, String value)
        {
            this.Name = name;
            this.Value = value;
        }
    }
}

Debugger;

SOLVED:

In the end it was human stupidity and careleness which led to this whole conundrum, I thank you all who helped me reach the right conclusion.

`nchar(20)` isn't what you want but won't cause that error. Where is the definition of `SPP`? What is the datatype of the param it creates? — Martin Smith, Aug 23 '15 at 19:05
@MartinSmith SPP is the stored procedure parameter, its attributes are name and value, hence why loc_nombre, name are passed down. I'll add the advance stored procedure code in my post. — Code Grasshopper, Aug 23 '15 at 19:10
it would be much easier if you don't hide the exception information by catching and rethrowing them. couldn't you at least use a debugger with exceptions enabled (when thrown) and see which code line is actually throwing? — Ivan Stoev, Aug 23 '15 at 19:21
@IvanStoev the problem seems to be happening on this dataAdapter.Fill(table); on the advance stored procedure. — Code Grasshopper, Aug 23 '15 at 19:32
Also replacing the line name.ToString() with "Local1" also yields the same error — Code Grasshopper, Aug 23 '15 at 19:38
Can you try using cmd.Parameters.Add() instead of AddWithValue()? Though it can't be a problem, but still try. — ssakash, Aug 23 '15 at 20:15
what about start eliminating potential failure points one by one? starting with removing the `WHERE` clause from the sp. — Ivan Stoev, Aug 23 '15 at 20:26
Are you sure you're connecting to the database and instance that you think you are? Not a different instance where the stored proc expects an int parameter? — Martin Smith, Aug 23 '15 at 20:31
[Can we stop using AddWithValue already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Felix Pamittan, Aug 23 '15 at 23:51
Instead of using `AddWithValue`, try using `var param = new SQLParameter("@" + p.Name, SqlDbType.NChar); param.Value = p.Value; cmd.Parameters.Add(param);` — Eris, Aug 23 '15 at 23:55
@ssakash, I'll do as you and Eris suggest, also there're no triggers in the database. Also I'll remove the where clause just to see how it behaves — Code Grasshopper, Aug 23 '15 at 23:58
@IvanStoev, removed the where clause and the exact same error is happenin, this leads me to belive the error is happening between the process of sending the parameter to the sp and when it get it — Code Grasshopper, Aug 24 '15 at 00:02
@Eris the problem with this is that I lose the ability to process different types on the same method, changing this make the other CRUD functions to crash. — Code Grasshopper, Aug 24 '15 at 00:06
Then see the @MartinSmith comment. This really looks like calling a sp which expects `int` parameter. — Ivan Stoev, Aug 24 '15 at 00:12
@IvanStoev, he was right someone on my team messed with the repositories and didn't notify me, thus I was working with a different MDF. — Code Grasshopper, Aug 24 '15 at 00:34
@Martinsmith, you where right if you want post your answer so I can accept and close this. — Code Grasshopper, Aug 24 '15 at 00:35
If you found the solution to the problem yourself you should post it as an answer and accept your own answer, or delete the question. — Zohar Peled, Aug 24 '15 at 08:27

score 0 · Answer 1 · answered Aug 25 '15 at 03:32

0

There was a problem with version control that duplicated the database, I was working with an outdated one.

answered Aug 25 '15 at 03:32

Code Grasshopper

610
1
11
29

Exception cannot convert nvarchar to int

1 Answers1