stored procedure not filling dataset

[Roy Lawson]

I have created a stored procedure on SQL Server 2000 that I know
works. I then dragged the sqldataadapter control onto my form, and
connected to an existing stored procedure. My datasets and sqlcommand
objects were created for me by the designer.

I pass in this (I used the values in the strDTStart, strDTEnd, and
strCompany variables succesfully in my test, it returned desired
results):

SqlSelectCommand4.Parameters(1).Value = strDTStart
SqlSelectCommand4.Parameters(2).Value = strDTEnd
SqlSelectCommand4.Parameters(3).Value = strCompany

SqlSelectCommand5.Parameters(1).Value = strDTStart
SqlSelectCommand5.Parameters(2).Value = strDTEnd
SqlSelectCommand5.Parameters(3).Value = strCompany

'##############################
conDaily.Open()
SqlSelectCommand4.ExecuteNonQuery()
SqlSelectCommand5.ExecuteNonQuery()

daLaborBudgets.Fill(DsLaborBudgets1)
DailyRoomSales.Fill(DsBudgetRoomSales1) '<--
BudgetRoomSales.Fill(DsRoomSales1) '<--
con.Close()
'###############################

The problem is that my datasets come back with nothing in them. The
code executes without an exception, however I am not filling the
datasets with what I know should come back. The SelectCommand
property for DailyRoomSales and BudgetRoomSales are set to
SqlSelectCommand4 and SqlSelectCommand5. This is automagicly
generated by VS.

I have followed everything I could find on the other newsgroups and am
about to give up on the stored procedure route, and just connect
directly to the SQL view and do the math programigicly

Any ideas on this one before I ditch the stored procedure?

Thanks,

Roy

 

[William Ryan eMVP]

Roy:

It's hard to tell b/c I can't see what's associated with each respective
dataadapter. What's going on with the Two ExecuteNonQuery statements?
Also, why three datasets? One should work but with that said, are you sure
that your table references are correct when you do the count? Are there any
other tables in your datasets that may be getting filled and it's just an
index difference when you use Table.Rows.Count?

It's not the fault of the Stored Prco and you don't want to go back to
dynamic sql. If your verification process is sound (that there are only the
number of tables in your datasets that you expect and that the Rows.Count is
referencing the correct table and the commands are referencing the right
dataadapters) then I'd run a trace on the Server and see what's going on.

Let me know about this and I'll see what we can do.


HTH,

Bill

 

[Val Mazur]

Hi Roy,

Your code looks strange like for me. Why do you call ExecuteNonQuery for the
commands? I do not think you need it to fill in datasets.. Since your code
is just a part of the whole picture, it is hard to say if everything is set
properly. What are your stored procedures look like? Do they suppose to
return any resultset? Could you post them here? Based on assumption that
code did not generate any exception I would say that your SPs do not suppose
to return any result set.
What I would suggest for you to do is to avoid any auto-generated code and
write code from the scratch. Stored Procedures work fine and I did not see
any problems using them. Refer next KB how to call SP in .NET


http://support.microsoft.com/default.aspx?scid=kb;en-us;308049&Product=adonet

 

[Roy Lawson]

<<What's going on with the Two ExecuteNonQuery statements?
Also, why three datasets? One should work but with that said, are you
sure
that your table references are correct when you do the count? Are there
any
other tables in your datasets that may be getting filled and it's just
an
index difference when you use Table.Rows.Count?>>

The third dataset uses the same connection and is getting filled without
any problems. I am creating a report that is getting data from 4
different tables in 2 seperate databases...coming from applications not
intended to work together (I managed to overcome this). As far as the
ExecuteNonQuery statements, I have removed them because they don't
belong.

I have several datagrids that reference each of the datasets (there are
2 more that you don't see in this code). Every single dataset is
getting filled except the ones that reference the stored procedures. I
am not forgetting to use the dataset.databind method on each dataset
btw. That is how I know the datasets are not getting filled (that and
the data doesn't show up in my crystal report)

When I get back to the office tomorrow I will post more code so you can
see the bigger picture.

Thanks for your input!

-Roy

 

[Kevin Yu [MSFT]]

Thanks for Bill and Val's quick response.

Hi Roy,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that there is no data filled to the DataSet
when you are executing stored procedure with SQL data provider. If there is
any misunderstanding, please feel free to let me know.

I have check the code you have provided in your post, just as Bill and Val
mentioned, we needn't call ExecuteNonQuery when we are fill data into a
DataSet. We can fill the DataSet with the following code:

DailyRoomSales.SelectCommand = SqlSelectCommand4
BudgetRoomSales.SelectCommand = SqlSelectCommand5

daLaborBudgets.Fill(DsLaborBudgets1)
DailyRoomSales.Fill(DsBudgetRoomSales1)
BudgetRoomSales.Fill(DsRoomSales1)

You can also check the KB article that Val mentioned in his post for more
information.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi Roy,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Roy Lawson]

Kevin, take a look at this:

DailyRoomSales.Fill(DsBudgetRoomSales1)
BudgetRoomSales.Fill(DsRoomSales1)

I tried to fill DsRoomSales1 using the wrong dataadapter (got them
reversed). It was such an idiotic mistake I couldn't believe it.

It should be:

DailyRoomSales.Fill(DsRoomSales1)
BudgetRoomSales.Fill(DsBudgetRoomSales1)

I was chasing my own tail for a bit. Thanks for the help, glad to know
that stored procedures are as easy to use in .NET as anything else.

-Roy

 

[Kevin Yu [MSFT]]

Hi Roy,

I'm glad to be helpful. Anybody can make such mistakes. Thanks for sharing
your experience with all the people here.
If you have any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."