By Terry Bottorff - 3/12/2010
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
|
By Terry Bottorff - 3/12/2010
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.
|
By Trent L. Taylor - 3/14/2010
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! 
|
By Terry Bottorff - 3/14/2010
I ended up putting the Select in the sproc and use the filldatatable(cmd). Works great. Thanks for the input.
|
By Trent L. Taylor - 3/14/2010
Glad you got it figured out
|