StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Stored Procedure Problem

Stored Procedure Problem

Terry Bottorff — Sun, 14 Mar 2010 21:00:49 GMT

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

RE: Stored Procedure Problem

Trent L. Taylor — Sun, 14 Mar 2010 21:00:49 GMT

Glad you got it figured out :)

RE: Stored Procedure Problem

Terry Bottorff — Sun, 14 Mar 2010 20:19:23 GMT

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

RE: Stored Procedure Problem

Trent L. Taylor — Sun, 14 Mar 2010 19:59:05 GMT

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! :)

RE: Stored Procedure Problem

Terry Bottorff — Fri, 12 Mar 2010 14:31:02 GMT

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.