Stored procedure returns no rows with SqlDataAdapter

Question

I'm new to using SqlDataAdpter and I'm trying to execute a stored procedure. The stored procedure executes successfully but no rows are returned. I've used SQL Server Profiler to monitor the call and it runs successfully (I can copy and execute the query from profiler without modifying it and get results).

I have the following:

public ActionResult Index()
{
    SqlConnection conn = null;
    DataSet results = null;

    try
    {
        string connectionString = // ... my connection
        conn = new SqlConnection(connectionString );
        string query = @"usp_mySP";

        conn.Open();

        SqlDataAdapter sqlAdpt = new SqlDataAdapter(query, conn);
        sqlAdpt.SelectCommand.CommandType = CommandType.StoredProcedure;

        var dataDate = new SqlParameter { ParameterName = "@DataDate", Value = DateTime.Now };
        var idList = new SqlParameter { ParameterName = "@IDList", Value = "1231,2324,0833" };                 

        sqlAdpt.SelectCommand.Parameters.Add(dataDate);
        sqlAdpt.SelectCommand.Parameters.Add(idList);

        results = new DataSet();
        sqlAdpt.Fill(results);

        sqlAdpt.Dispose();
    }
    catch (SqlException e)
    {               
        throw new Exception("Exception:" + e.Message);
    }
    finally
    {
        if (conn != null)
            conn.Close();
    }

    return View(results);
}

When I inspect the DataSet through the debugger, it always returns 0 rows.

Please help with what I'm doing wrong?

Note: I've also tried (but do NOT prefer) executing as a SQL command:

EXEC usp_mySP @DataDate, @IDList

and it didn't work either as I got int to varchar conversion errors.

Perhaps verify that you have set nocount on at the beginning of your SP. — user212514
– user212514, Commented Jun 24, 2016 at 16:43
Tip for debugging: Use sql server profiler and verify what is sent to the sql server instance. Also see what the return is (should have rows or some other property). You can also capture the exact sql command and reexecute in management studio to see what the result was. — Igor
– Igor, Commented Jun 24, 2016 at 16:50
did you use SQL Profiler to capture what SQL is going to server? Did you debug your SP? It may be helpful to see your logic inside your SP. Sometimes the date format sent by .Net may not be matching up with the date format expected by DB Server!! — techspider
– techspider, Commented Jun 24, 2016 at 16:51
Run your program in debug mode and see what value you get for 'connectionString' - from the looks of your code, string connectionString = ... doesn't seem to have any value set. The line is not terminated with ';' — SRQ Coder
– SRQ Coder, Commented Jun 24, 2016 at 16:55
It was the set nocount on. It was not there. It now works Thanks! — usr4896260
– usr4896260, Commented Jun 24, 2016 at 16:57

marc_s · Accepted Answer · 2016-06-24 16:58:04Z

1

I think you try to add SqlParameter using SqlCommand like this :

SqlCommand cmd = new SqlCommand();
cmd.parameter.addwithvalue(@DataDate,DateTime.Now);

edited Jun 24, 2016 at 16:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 24, 2016 at 16:45

Ravi Shah

87314 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

That's a valid way to do this - but so is the approach used in the question. This is definitely not the reason for the issues ....

usr4896260 · Accepted Answer · 2016-06-24 17:00:06Z

1

So the reason was because of set nocount on. I added it to my sp and it works. Thank you everyone for clarifying.

answered Jun 24, 2016 at 17:00

usr4896260

1,5073 gold badges30 silver badges57 bronze badges

Collectives™ on Stack Overflow

Stored procedure returns no rows with SqlDataAdapter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related