I have a Stored Proc that works fine from SSMS. In general this is what the Stored Proc looks like:

1. Deletes data in Table A.

2. Inserts data in Table A with an Insert ... Select ...

3. Deletes data in Table B.

4. With a Parameter it Inserts Data in Table B with an Insert ... Select ...



The Data in Table B is from Table A using a Group By. The BO is based on Table B and remains empty when I try to fill it with the following commands:

Dim cmd As New SqlCommand("sp_gborderbyavegoonegotwo")

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@rodeoid", SqlDbType.Int).Value = nrod

Me.FillDataTable(cmd)



nrod is defined as an integer and has the correct value. Of course I don't know if a stored proc can have multiple statements but since it worked in SSMS I thought it would work with a BO and I tried to find the info on the forum but could not. Thank you for any advise you can give me. If you need more info please let me know. TIA

I solved my problem but I am not sure I did it correctly so if someone could let me know what the correct way to do this is or if there is a better way.



I used the following:

Me.ExecuteScalar(cmd)

Me.FillDataTable("Select * from TimesPointsTempAverage")



TIA.

I would really have to take a look at ths SPROC to give you a more definite answer.  It looks like you are calling the scalar first which would return a single value then calling a fill which would return a result set.  This would ultimately be 2 result sets when coming from a single SPROC.  My rule of thumb is the fewer trips that you make to the server the better off....so if you could do this in a single SPROC, you would be better off.  But like I said, I would have to have a slightly better grasp of what you are trying to do.  Your logic will most likely work just fine for the long term, but if you could consolidate this into a single trip, that never hurts!

I ended up putting the Select in the sproc and use the filldatatable(cmd). Works great. Thanks for the input.

Glad you got it figured out