By Clayton Hoyt - 11/9/2006
I have a BO setup to use OptimisticAllFields on Update. I use stored procedures generated by the DDT but when I run an update I get the below error:"MicroFour.StrataFrame.Data.DataLayerSavingException: Procedure or Function 'ha_rfp_update' expects parameter '@PROSPECT_NAME_ORG', which was not supplied. ---> System.Data.SqlClient.SqlException: Procedure or Function 'ha_rfp_update' expects parameter '@PROSPECT_NAME_ORG', which was not supplied. -- Yada yada ---- at MicroFour.StrataFrame.Business.BusinessLayer.Save(Boolean Transactional, String TransactionKey) at MicroFour.StrataFrame.Business.BusinessLayer.Save() at RFPEdit.SaveRFP() in D:\TMG\HA\RFP\WEB\RFPEdit.aspx.vb:line 120" What am I doing wrong here? Thanks Clay
|
By Trent L. Taylor - 11/9/2006
What do your CRUD settings look like on the BO? It sounds like your CRUD settings are not setup to match the stored procedure.
|
By Clayton Hoyt - 11/9/2006
Unfortunatly, they look good...just like all the others I have that do work. The Insert works fine...I've not tried the Delete but I'd imagine it would work also. All of the sprocs were created using the DDT and I've verified that they are spelled right (I'd think if it were misspelled I'd be getting other errors anyway). I recreated them to make sure something hadn't changed. I tried rebuilding...screaming...rebooting... 
|
By Clayton Hoyt - 11/9/2006
Here is piece of the code that won't workIf intRFPID = 0 Then.NewRow() Else.FillRFPByID(intRFPID) .Edit()End If.PROSPECT_NAME = Me.txtProspect.Text .COMMENTS = Me.txtComments.TextIf .Save() <> MicroFour.StrataFrame.Data.SaveUndoResult.Success Then Me.lblResult.Text = "The record was NOT saved."
|
By Trent L. Taylor - 11/10/2006
Well, just to remove any confusion let me give you a quick overview so that you can make sure all of the BO properties are set properly.Database Deployment Toolkit
Here is a table where I am allowing the DDT to create my stored procedures. 
In this example, my table name is TestNames and you can see the default names that are generated. You can rename the stored procs, but if you do you will need to specify the procs names on the BO explicitly. BO CRUD Settings
Here are the CRUD settings that are set within the property sheet of my business object. 
In this scenario, this is all that needs to be done in order to properly update your table using a sproc. Notice that I did not specify the InsertStoredProcedureName or UpdateStoredProcedureName. The reason I could do this is because of the default naming schema expected when using the DDT. If the sprocs do not match the schema: TableName_Insert, TableName_Udpate, and tableName_Delete....then you will need to set the StoredProcedureName properties.
|
By Trent L. Taylor - 11/10/2006
One other thing to look at...open the stored procedure through the SQL Server Management Studio (or Enterprise Manager) and let me look at it here. Also, I will need the table structure so I have something to compare it to.
|
By Clayton Hoyt - 11/10/2006
Hi TrentHere are my DDT settings 
|
By Clayton Hoyt - 11/10/2006
Trent...I sent the sproc to your email.
|
By Trent L. Taylor - 11/10/2006
Clayton...can you try something for me. Before I try to dig out all of the stored procs settings, can you add a row version field to you table and attemt to use Row versioning. The WHERE clause of your stored proc will basically go to nothing...but I just want to see if this resolves your problem.Just add an integer field in your table. Open the table properties and indicate that you are going to use row versioning and provide the version field. (obviously deploy your structure). Go to the CRUD settings on your BO and change the following properties: UpdateConcurrencyType = OptimisticRowVersion
RowVersionOrTimestampColumn = "YourRowVersionFieldName" Rebuild and try this again and see if the error persists.
|
By Clayton Hoyt - 11/10/2006
Hi TrentI created a field name ROWVER as an INT and changed the options to use RowVersioning. Now it tells me that the sproc needs @ROWVER but its not provided.
|
By Trent L. Taylor - 11/10/2006
That's a good sign....what does your update sproc look like now? Does it have the ROWVER parameter in the where clause? If not, then your sprocs are not getting deployed or rebuilt properly.Just one other thing...you did set the table properties to RowVersioning and provided the ROWVER field...correct?
|
By Clayton Hoyt - 11/10/2006
I tried using a sproc with only the Row ID (an Identity field) as the WHERE clause and changing the BO to not use any concurrency and it works fine.
|
By Clayton Hoyt - 11/10/2006
In the BO, I set the optimistic concurrency to rowversioning and then entered the name of the field (ROWVER) in the rowversion name field.
|
By Clayton Hoyt - 11/10/2006
Sorry...I didn't read your whole post earlier...yes...the parameter is in the sproc. I was assuming that is why I'm getting the error from the data layer.
|
By Trent L. Taylor - 11/10/2006
I know that you know how to do this....but I went ahead and created a sample for you to compare your settings to. You can import the DDT package file with this sample into a DDT project. The package file is in the Bin folder of the sample and is named SprocTest.pkg.Just build this and it will ask you if you want to install the database for the sample when it launches.
|
By Clayton Hoyt - 11/10/2006
 ...thanks for the diplomatic response!I've done this quite a few times before without issue so I'm certain that I have something dorked up. For the moment the clock is ticking so I just took conconcurrency off. This is a beta release and not the end of the world. I'll revisit it during a bug fix later next week. Clay
|
By Clayton Hoyt - 11/16/2006
Ok..I know you are thinking I'm an idiot...but I have gone through this several times end to end and manually compared each step an everything is right. I have done this MANY times with other businesss objects in the past without an issue. The error I am getting is that the business object cannot find PROSPECT_NAME_ORG. PROSPECT_NAME is the first field in the table and the first parameter in the sproc; therefore, PROSPECT_NAME_ORG is the first _ORG field in the sproc. It seems to me like the .SAVE is not sending along ANY of the _ORG parameters. Here is what I've done - Manually compared all of the fields in my DDT package with the production table and everything is in perfect order and correct down to the field types and sizes
- Verified that Optimistic All Fields is set in the DDT for my table update.
- Verified that the _ORG parameters are in the sproc
- Verified that Optimistic All Fields is set as the Update Concurrency Type for this BO
- Verified that Update Using Stored Procedure is set to TRUE
- Rebuilt several times
- Cleaned the app
- Deleted the DLLs and rebuilt
- As a last ditch effort, I have tried running a trace but it is not getting that far so that was a waste of time.
The sproc has all of the expected _ORG parameters in it, so I am extremely confident that that is not the problem. Since I have the parameters all checked, I am not sure what to do.
|
By Clayton Hoyt - 11/16/2006
I have also tried the following and IT WORKS- Using the DDT, take Optimistic Concurrency off in Table Properties and rebuild the sprocs
- Refresh the production database with the new sproc that contains not _ORG elements
- Changed my BO to NOT use optimistic concurrency
That was ALL I did and now I can update records again (without any protection for concurrency issues).
|
By StrataFrame Team - 11/16/2006
The only thing I can think of Clayton, is that maybe your sprocs were configured to something other than OptimisticAllFields and the business object was configured to OptimisticAllFields. Your best scenario would be to add a timestamp or row version column and configure both the business object's UpdateConcurrencyType and the concurrency type of the Update stored procedure to the same (either OptimisticTimeStamp or OptimisticRowVersion).
|
By Clayton Hoyt - 11/16/2006
Hi BenPlease understand that I am not trying to be argumentative but that doesn't make sense. So far I have... - Verified that the DDT is using Optimistic Concurrency
- Verified that the sproc has all of the required fields parameters (2 for each field...one regular/one _ORG). I have manually gone through a printed version of this and checked them off one at a time to make sure that somehow something didnt get lost.
When you read the error, the sproc is not the problem anyway. It is expecting _ORG params and they are NOT being supplied. The .SAVE in the BO is supposed to do the supplying...and the only way I know of to make it do the supplying is to set Optimistic Concurrency in the BO to true (If that is not the case, please let me know). I have made several changes to the BO (adding and deleting fields) over the last few weeks and the problem did not start until after I had done that.
|
By StrataFrame Team - 11/16/2006
Did you rebuild the partial class for the business object after you added the fields? There are collections within the partial class that the BO uses when saving the data (that's how it determines what fields to save). Also, there are 3 different types of optimistic concurrency, OptimisticAllFields, OptimisticTimeStamp, and OptimisticRowVersion. The UpdateConcurrencyType property on the business object, and the UpdateConcurrencyType of the Sproc (set within the table properties in the DDT) have to match exactly... if the BO thinks it's on row version and the sproc is configured for all fields, then it will throw up. Also, the properties for your business object can be set within both the component designer for your bo and the property sheet on the individual business object. Make sure that the property sheet on the instance of the business object is not overriding the property you have set on the component designer.
|
By Clayton Hoyt - 11/16/2006
Hi BenMy responses are below in bold. Thanks for the help. I am completely open to notion that I've screwed something up in this...I am just at a loss as to where to look. My next step is to just build the whole BO from scratch again but don't have time to do that at the moment. Did you rebuild the partial class for the business object after you added the fields? There are collections within the partial class that the BO uses when saving the data (that's how it determines what fields to save). Yes..I tried both Partial and Full rebuilds. Additionally, I have added several fields over the last couple of weeks and these are all available from my business objects as properties. I'd think that this would not be the case if I'd not rebuilt the BOs. Also, there are 3 different types of optimistic concurrency, OptimisticAllFields, OptimisticTimeStamp, and OptimisticRowVersion. The UpdateConcurrencyType property on the business object, and the UpdateConcurrencyType of the Sproc (set within the table properties in the DDT) have to match exactly... if the BO thinks it's on row version and the sproc is configured for all fields, then it will throw up. They are both set to OptimisticAllFields. There are 20 or so _ORG parameters in the stored proc. I'd imagine that if TimeStamp or RowVersion were being used, there wouldn't be that many (only one?). Also, the properties for your business object can be set within both the component designer for your bo and the property sheet on the individual business object. Make sure that the property sheet on the instance of the business object is not overriding the property you have set on the component designer. I didn't know you could do this and certainly had not coded it directly...I did check though and didn't find anywhere that was being done in code I had written.
|
By StrataFrame Team - 11/16/2006
Well, Clayton, what you have here is a certifiable "gremlin." You can't find it, but you know it's there 
|
By Clayton Hoyt - 11/16/2006
Thanks BenThat is what I was afraid you were going to say! I'll just reubild the thing when time permits. For the moment, I'll move on without concurrency and pray for the best!
|