Dynamic SQL and Datatable

  • Thread starter Thread starter Jason MacKenzie
  • Start date Start date
J

Jason MacKenzie

I have a question about populating a datatable from a stored proc. I have a
dynamic Sql statement that I'm building that I execute using the exec
command to populate a temporary table. I then do a select from that table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never has any rows in
it (no exception is thrown). I'm thinking it must be because it stops after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason
 
Jason, check the table count of your dataset, I'll be you have two
datatables. The second one should have some values in it.
 
I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is going to be
a real pain!

Thanks for the tip. I think you've pointed me in the right direction.
 
It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the dataset and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com
 
The problem is that I have set up the application to use datatables instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called all
over the place.

So before I change them all to datasets for this one situation I'm going to
try to come up with a better approach.

William Ryan eMVP said:
It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the dataset and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

Jason MacKenzie said:
I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is going to be
a real pain!

Thanks for the tip. I think you've pointed me in the right direction.


I
have rows
Click to expand...
Click to expand...
 
Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second one may
be rejected. If that's in fact the problem, we can work around it but let's
verify it first.
Jason MacKenzie said:
The problem is that I have set up the application to use datatables instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called all
over the place.

So before I change them all to datasets for this one situation I'm going to
try to come up with a better approach.

William Ryan eMVP said:
It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the dataset and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

Jason MacKenzie said:
I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is going
Click to expand...
to
be
a real pain!

Thanks for the tip. I think you've pointed me in the right direction.


Jason, check the table count of your dataset, I'll be you have two
datatables. The second one should have some values in it.
I have a question about populating a datatable from a stored proc. I
have
a
dynamic Sql statement that I'm building that I execute using the exec
command to populate a temporary table. I then do a select from that
table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never has any rows
in
it (no exception is thrown). I'm thinking it must be because it stops
after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging
purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason
Click to expand...
Click to expand...
Click to expand...
 
Interesting: I populated a dataset. Table count is 1 and the rowcount for
that table is 20 which is correct. The next line populates a datatable with
the same sql statement and the row count is 0.

I have no idea what to make of that.



William Ryan eMVP said:
Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second one may
be rejected. If that's in fact the problem, we can work around it but let's
verify it first.
Jason MacKenzie said:
The problem is that I have set up the application to use datatables instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called all
over the place.

So before I change them all to datasets for this one situation I'm going to
try to come up with a better approach.

William Ryan eMVP said:
It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the
Click to expand...
dataset
and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is
Click to expand...
Click to expand...
going
to proc.
I
Click to expand...
Click to expand...
 
What next line?
Jason MacKenzie said:
Interesting: I populated a dataset. Table count is 1 and the rowcount for
that table is 20 which is correct. The next line populates a datatable with
the same sql statement and the row count is 0.

I have no idea what to make of that.



William Ryan eMVP said:
Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second one may
be rejected. If that's in fact the problem, we can work around it but let's
verify it first.
Jason MacKenzie said:
The problem is that I have set up the application to use datatables instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called all
over the place.

So before I change them all to datasets for this one situation I'm
Click to expand...
going
to
try to come up with a better approach.

It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the dataset
and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one
change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is
Click to expand...
going
to
be
a real pain!

Thanks for the tip. I think you've pointed me in the right direction.


Jason, check the table count of your dataset, I'll be you have two
datatables. The second one should have some values in it.
I have a question about populating a datatable from a stored proc.
I
have
a
dynamic Sql statement that I'm building that I execute using the
exec
command to populate a temporary table. I then do a select from that
table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never has any
rows
in
it (no exception is thrown). I'm thinking it must be because it
stops
after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging
purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason
Click to expand...
Click to expand...
Click to expand...
 
Sorry - that was really vague. I have an object that I call the methods from

dim dsDataSet as dataset = objCommon.returnDataSet(strSQL)

dim dtDataTable as datatable = objCommon.returnDataTable(strSQL)

dsDataSet has 1 table with 20 Rows
dtDataTable has no rows

These two methods work fine - I've used them for a long time with no issues.
But that's what I meant by "next line"

I appreciate all the time you're taking.



William Ryan eMVP said:
What next line?
Jason MacKenzie said:
Interesting: I populated a dataset. Table count is 1 and the rowcount for
that table is 20 which is correct. The next line populates a datatable with
the same sql statement and the row count is 0.

I have no idea what to make of that.



William Ryan eMVP said:
Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second
Click to expand...
one
may
be rejected. If that's in fact the problem, we can work around it but let's
verify it first.
The problem is that I have set up the application to use datatables
instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is
Click to expand...
called
all
over the place.

So before I change them all to datasets for this one situation I'm going
to
try to come up with a better approach.

It shouldn't be a pain at all b/c you'll just have two tables in it.
You
can set the reference to your existing table to table[1] in the dataset
and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match,
however,
just to test, go ahead and make a dataset, call fill on it and
Click to expand...
check
the
table count. That can be done with just one new line of code and one
change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying
definitely
could be true. Maybe I do need to use a dataset. Which if so is going
to
be
a real pain!

Thanks for the tip. I think you've pointed me in the right direction.


Jason, check the table count of your dataset, I'll be you have two
datatables. The second one should have some values in it.
I have a question about populating a datatable from a stored proc.
I
have
a
dynamic Sql statement that I'm building that I execute using the
exec
command to populate a temporary table. I then do a select from
that
table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never
Click to expand...
has
any
rows
in
it (no exception is thrown). I'm thinking it must be because it
stops
after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging
purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason
Click to expand...
Click to expand...
Click to expand...
 
I made a hacky change to get around the issue right now. I'm just going to
check to see if my datatable has any rows and return it if it does. If not
I'll create a dataset and return the first table that has any rows. It
seems to be working so far although I'd really like to know why this is
happening in the first place.


Jason MacKenzie said:
Sorry - that was really vague. I have an object that I call the methods from

dim dsDataSet as dataset = objCommon.returnDataSet(strSQL)

dim dtDataTable as datatable = objCommon.returnDataTable(strSQL)

dsDataSet has 1 table with 20 Rows
dtDataTable has no rows

These two methods work fine - I've used them for a long time with no issues.
But that's what I meant by "next line"

I appreciate all the time you're taking.



William Ryan eMVP said:
What next line?
Jason MacKenzie said:
Interesting: I populated a dataset. Table count is 1 and the rowcount for
that table is 20 which is correct. The next line populates a datatable with
the same sql statement and the row count is 0.

I have no idea what to make of that.



Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second one
may
be rejected. If that's in fact the problem, we can work around it but
let's
verify it first.
The problem is that I have set up the application to use datatables
instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called
all
over the place.

So before I change them all to datasets for this one situation I'm going
to
try to come up with a better approach.

It shouldn't be a pain at all b/c you'll just have two tables in it.
You
can set the reference to your existing table to table[1] in the
dataset
and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match,
however,
just to test, go ahead and make a dataset, call fill on it and check
the
table count. That can be done with just one new line of code
Click to expand...
and
one
change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying
definitely
could be true. Maybe I do need to use a dataset. Which if so is
going
to
be
a real pain!

Thanks for the tip. I think you've pointed me in the right
direction.


Jason, check the table count of your dataset, I'll be you
Click to expand...
have
two
datatables. The second one should have some values in it.
I have a question about populating a datatable from a stored
proc.
I
have
a
dynamic Sql statement that I'm building that I execute
Click to expand...
using
the
exec
command to populate a temporary table. I then do a select from
that
table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never has
any
rows
in
it (no exception is thrown). I'm thinking it must be
Click to expand...
because
it
stops
after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging
purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason
Click to expand...
Click to expand...
Click to expand...
 
Back
Top