C# Query : Failed to populate DataTable

A

AmjiBhai

Joined
Feb 21, 2018
Messages
216
Reaction score
86
The following lines have all of a suddan stopped bringing data into the dataTable [listProcDetails]

cmd.CommandText = "select name,object_id,Create_date,modify_date,@@ServerName from sys.procedures";
cmd.CommandType = CommandType.Text;
con.Open();
DataTable listProcDetails = new DataTable();
listProcDetails.Load(cmd.ExecuteReader());
con.Close();

Whereas when I try the same SQL "select name,object_id,Create_date,modify_date,@@ServerName from sys.procedures" in ssms...
it lists down all the procedures...

I guess I am missing something...Please help.
 
I see some code is missing. Did you connect the con object with the cmd object? It seems as if you didn't connect them yet. :user:
 
Abraham Andres Luna said:
I see some code is missing. Did you connect the con object with the cmd object? It seems as if you didn't connect them yet. :user:
Click to expand...
No.... The code is preceeded by two using commands as follows

using (SqlConnection con = new SqlConnection(@SqlCon_Str))
{
using (SqlCommand cmd = con.CreateCommand())
{

It's was working fine in the morning...I tested it..... Something went wrong
 
Abraham Andres Luna said:
Okay, so if it worked before, maybe there is a permissions issue. What user are you connecting to the DB with?
Click to expand...
I have two servers which are located in the same office premises... I connect to them with user sa...being a super admin user Can't expect permission issue .
 
Abraham Andres Luna said:
Okay so both servers have the same database? And both have some stored procedures?
Click to expand...
Yes...I wanted to reconcile the two listings of stored procedures....The both database structures are identical.
I was lucky to have backup of my old source code which was successfully picking data....Short while ago I ran that code and glad to see its still picking the data from sql server and placing it in data table successfully....

The difference between the two is just that in the new script i am using the 'using' for SqlCon and Sql command where as in the old i am doing it without using the 'using'

Old script that works fine is as follows:

SqlConnection SqlCon = new SqlConnection(@SqlCon_Str);
var cmd = new SqlCommand("select name,object_id,Create_date,modify_date,@@ServerName from sys.procedures", SqlCon);
cmd.CommandType = System.Data.CommandType.Text ;
SqlCon.Open();
DataTable listJobDetails = new DataTable();

Whereas, in the new script I pretended to adopt a sober way (and failed!):

using (SqlConnection con = new SqlConnection(@SqlCon_Str))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{

cmd.CommandText = "MyList";
cmd.CommandType = CommandType.StoredProcedure;
DataTable listProcDetails = new DataTable();
listProcDetails.Load(cmd.ExecuteReader());
 
Last edited:
I think your problem is opening the SqlCon before the using(SqlCommand) line. In the code that works you Open the SqlCon after you set the CommandType :user:
 
Back
Top