CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduresvarchar
Blair
Blair

Reputation: 113

ExecuteReader not returning anything

I have a table called 'MatchType' and it contains: 

ID | MatchTypeName
1  | One Day
2  | Two Day
3  | T20

I have a method to retrieve a record from that table based on a sting that will match a value in MatchTypeName:

public static int GetByName(string matchType)
{          
    MatchType item = new MatchType();

    using (SqlConnection con = new SqlConnection(BaseDataAccessLayer.GetConnectionStringByName()))
    {
        using (SqlCommand cmd = new SqlCommand(STORED_PROC_GetByName, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@matchTypeName", SqlDbType.VarChar, 20).Value = matchType;
            con.Open();

            using (IDataReader reader = cmd.ExecuteReader())
            {
                item.LoadFromReader(reader);
            }
        }
    }

    return item.Id;
}

This method calls this stored procedure:

CREATE PROCEDURE [dbo].[MatchType_GetByName]
    (@matchTypeName varchar(20))
AS
BEGIN
    SET NOCOUNT ON

    SELECT 
        [Id], [MatchTypeName]
    FROM 
        [dbo].[MatchType]
    WHERE 
        [MatchTypeName] = @matchTypeName

    RETURN @@ERROR
END

When calling the GetByName method I am passing in a string "One Day". I have confirmed this through debugging. I can't post an error message that is helpful as there isn't one being generated. Just no data is being returned in the reader...

I have a couple of other methods and stored procedures that use a similar process and they work. The only thing that I can see is the difference is that I am using the WHERE clause on a varchar/string value which is something that the other queries don't do...

Yet i can run this stored proc and it returns all the records..

BEGIN
    SET NOCOUNT ON

    SELECT
        [Id],
        [MatchTypeName]
    FROM
        [dbo].[MatchType]
    RETURN @@ERROR
END

Upvotes: 1

Views: 2390

Answers (3)

shadow
shadow

Reputation: 1903

Try this:

using (SqlCommand cmd = new SqlCommand(STORED_PROC_GetByName, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@matchTypeName", matchType);
            con.Open();

            using (IDataReader reader = cmd.ExecuteReader())
            {
                item.LoadFromReader(reader);
            }
        }

SqlCommand.Parameters Property

Upvotes: 0

EvilBob22
EvilBob22

Reputation: 732

The procedure is running the query and showing the results (which is why it works in SSMS), but doesn't actually return anything. Try this (note, I haven't actually tested it).

CREATE PROCEDURE [dbo].[MatchType_GetByName]
    @matchTypeName varchar(20),
    @matchTypeID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 
        @matchTypeID = [Id], [MatchTypeName]
    FROM 
        [dbo].[MatchType]
    WHERE 
        [MatchTypeName] = @matchTypeName

    RETURN @@ERROR
END

Upvotes: 0

Szymon
Szymon

Reputation: 43023

The default length for varchar declared without size is 1. You declared it like that in your stored proc parameter so you're only passing the first character.

Change your declaration:

(@matchTypeName varchar)

to a relevant size:

(@matchTypeName varchar(20))

Also, add the size in the code and make sure that the case in your parameter name matches your stored proc:

cmd.Parameters.Add("@matchTypeName", SqlDbType.VarChar, 20).Value = matchType;

Upvotes: 7

Related Questions