C# Query : Failed to populate DataTable


Joined
Feb 21, 2018
Messages
209
Reaction score
83
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.
 
Ad

Advertisements

Joined
Mar 14, 2018
Messages
609
Reaction score
197
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:
 
Joined
Feb 21, 2018
Messages
209
Reaction score
83
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:
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
 
Joined
Mar 14, 2018
Messages
609
Reaction score
197
Okay, so if it worked before, maybe there is a permissions issue. What user are you connecting to the DB with?
 
Joined
Feb 21, 2018
Messages
209
Reaction score
83
Okay, so if it worked before, maybe there is a permissions issue. What user are you connecting to the DB with?
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 .
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
209
Reaction score
83
Okay so both servers have the same database? And both have some stored procedures?
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:
Joined
Mar 14, 2018
Messages
609
Reaction score
197
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:
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top