CODEWITHSUNDEEP
c#sql-servert-sql
Lucky
Lucky

Reputation: 111

C# & SQL Server stored procedure doesn't return results

I'm trying to get a zip from a database and I've written a stored procedure like this

[dbo].[p_SearchZipLocal]
    @zip nvarchar(10) = NULL,
    @city nvarchar(100) = null
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN
        -- Verifica se o zip aparece na primeira coluna
        IF((SELECT COUNT(cttzip) 
            FROM seur 
            WHERE cttzip = @zip) > 0)
        BEGIN
            -- Verifica se o valor é NA, se não for devolve o zip e a localidade correcta
            IF((SELECT TOP(1) [SubCity1] as city 
                FROM seur 
                WHERE cttzip = @zip) = '#N/A')
            BEGIN
                -- Se for NA vamos ter de pesquisar na quinta coluna pela localidade
                IF((SELECT count(*) FROM seur WHERE subCity2 LIKE @city + '%' )>0)
                    -- Se encontrar-mos devolvemos o novo zip
                    SELECT TOP(1) 
                        '0' AS Error, subZip2 as zip, subCity2 AS city 
                    FROM seur 
                    WHERE subCity2 LIKE @city + '%'
                ELSE
                    SELECT 
                        '2' AS Error, 'Erro' AS zip, 'Erro' AS city
                END
            ELSE
                SELECT TOP(1) 
                    '0' AS Error, cttzip AS zip, [SubCity1] as city 
                FROM seur 
                WHERE cttzip = @zip
        END
    ELSE
        SELECT '1' AS Error, 'Erro' AS zip, 'Erro' AS city
    END
END

Returning the results when executed from SQL Server like this:

0   9900    AEROPORTO DA HORTA

But when I call it from C# code it doesn't return the results to the datatable.

using (SqlCommand cmd = new SqlCommand("p_SearchZipLocal", connectionSQL))
{
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = "9900";
    cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = "PRAIA DO ALMOXARIFE";

    connectionSQL.Open();

    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
}

This code gets the column names in the return but there is no value associated. Can anyone tell me where is my code wrong?

Upvotes: 1

Views: 385

Answers (2)

Avinash Jain
Avinash Jain

Reputation: 7616

The DataTable.Load method expects a primary key column in the underlying data (i.e. from DataReader). Look like your procedure not have any primary key column, or if you have one please user order by in sql statement and try to keep it as first column so that DataTable will able to accept it as primary.

This is very old issue with DataTable.Load and not very well documented. In general SQLDataAdapter is good with DataTable.

Upvotes: 1

FormulaChris
FormulaChris

Reputation: 101

I would use .Fill with a DataAdapter

Please see this...

DataTable.Load(FbDataReader) does not load everything into DataTable

Upvotes: 1

Related Questions