My stored procedure returns something, but sqlDataAdapter.Fill leaves datatable empty
Something stopped working in my application. I tested my stored procedure and it does return 3 rows (too big to repost here). At the output in Visual Studio it says afterwards:
(1 row(s) affected)
(3 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[GetCaseDocumentFile].
Why can't I put it into the datatable called dtResult? The command sqlDataAdapter.Fill(dtResult) should do it. Instead I get {} when looking at it in debug mode.
string connectionString = @"Data Source=34.56.55.251;Initial Catalog=DBXXX;User ID=xx;Password=XXXXXXXX;";
string queryString = "GetCaseDocumentFile";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@key", 200012);
connection.Open();
// Works! Three lines are returned.
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
Console.WriteLine(String.Format("{0}, {1}",
reader[0], reader[1]));
}
}
finally
{
reader.Close();
}
// Does not work! dtResult remains empty. {}
DataTable dtResult = new DataTable();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command);
sqlDataAdapter.Fill(dtResult);
DataTable result = dtResult;
}
Update:
Nevermind. This actually works. This is my test code and what I thought my real code does. But I guess that I overlooked开发者_JS百科 something in the real code.
Have You set no count on in your Procedure??
Set NoCount On;
精彩评论