Hi all.

This one has me baffled. I have a BO based on a view because the table in SQL server (Sleep.AccountMaster) has fields in it that contain spaces and the BO Mapper pukes on them. I am updating it from the view via stored procedure. Here is my stored procedure:

ALTER PROCEDURE [dbo].[prcUpdPatient] @Phone VARCHAR(50), @Status VARCHAR(50), @EmailAddress VARCHAR(50), @NextCalldate DATE, @CStateProviderHold BIT, @CStateReturnedEquipment BIT, @CustomerPatientID VARCHAR(50)

AS

BEGIN TRY

DECLARE @RetVal INT

/* Update the record */

UPDATE Sleep.AccountMaster SET Status = @Status,

[Email Address] = @EmailAddress,

[Next Call Date] = @NextCalldate,

CStateProviderHold = @CStateProviderHold,

CStateReturnedEquipment = @CStateReturnedEquipment

WHERE CustomerPatientID = @CustomerPatientID

SET @RetVal = @CustomerPatientID

END TRY

BEGIN CATCH

/* Get the details of the error*/

EXEC prcLogError

SET @RetVal = -1

END CATCH

IF @RetVal < 0

BEGIN

DECLARE @ErrText VARCHAR(1000)

SET @ErrText = 'Unable to Update the ' + @CustomerPatientID + ' Account Master Rcord'

RAISERROR( @ErrText, 16, 1 )

END

RETURN @RetVal

Now, the goofy thing is that if I run this update statement directly in a query window:

update Sleep.AccountMaster

set Phone='7086367918',

Status='manual',

[Email Address]='marcia@tightlinecomputers.com',

[Next Call Date]='2011-03-06',

CStateProviderHold=1,

CStateReturnedEquipment=1

where CustomerPatientID='53-57933'

The update happens with no errors at all.

Could the problem be caused by the fact that the table AccountMaster belongs to the Sleep schema but the SP and the view belong to dbo?

Here are the error details. Hopefully someone can help me figure this one out.

BusinessLayerException
  An error occurred while saving an the data to the server.
DataLayerSavingException
  Unable to Update the 53-57933 Account Master Rcord
SqlException
  Unable to Update the 53-57933 Account Master Rcord

Source     : MicroFour StrataFrame Business

Stack Trace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at MicroFour.StrataFrame.Data.DbDataSourceItem.InternalExecuteNonQuery(DbCommand Command, Boolean IsTransactional, String TransactionKey)
   at MicroFour.StrataFrame.Data.SqlDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler, Boolean RecreateCommand)
   at MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler)
   at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)
   at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTable(DataTable TableToUpdate, Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.Data.DataLayer.SaveByForm(DataTable TableToSave, Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.Business.BusinessLayer.SaveByForm(Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save(Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save()
   at MicroFour.StrataFrame.UI.Windows.Forms.MaintenanceFormToolStrip.cmdSave_Click(Object sender, EventArgs e)
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
   at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


TIA

Marcia

Could the problem be caused by the fact that the table AccountMaster belongs to the Sleep schema but the SP and the view belong to dbo?

Yes, this could be part of it.  When updating a view, you have to make sure that the view is bound to the schema, otherwise it cannot be updated.  So when you are dealing with two different schemas, this can most definitely cause a problem. 

When dealing with an issue like this you may need to create a SPROC specific to accommodating the update.  You can then create a method in the BO that enumerates the rows and manually updates or inserts each row.  I will generally create an UPSERT SPROC so that I can have a one stop shop for INSERTS and UPDATES when dealing with a unique situation like this.

I am not updating the view. I created the view because the table on which it is based has spaces in the field names and the BO Mapper puked on that. So I created the view to remove the spaces. Then, in my stored procedure I am taking the parameters coming from my view-based BO to update the actual table.

Why is this such a problem? As I said, the update statement works fine when executed directly in Management studio.

TIA.

Marcia

It shouldn't be a problem if you are not updating.  So you have the BO mapped to the view.  An you are receiving this error during a query?  I guess I am a bit confused because the error is showing an update error:

Unable to Update the 53-57933 Account Master Rcord

If you are trying to update, then you have to make sure that your SPROC lines up with the BO.  The best way to see this is if you use the DDT and let it deploy a structure for you and allow it to create the INSERT and UPDATE sprocs.  You can then open it up and "steal" the INSERT and/or UPDATE parms that are created.  The BO will expect the PARMS of the fields going into it.  So they will need to exist.  Next, you need to set the CRUD settings on the BO to account for your INSERT and/or UPDATE sproc.  You will also need to specify the sproc name since it will most likely not be a standard name expected by the BO.

That error is generated by my inernal error handler

What I finally did was to talk the project manager into removing the spaces in the table I was trying to update and everything started to work

I do not own the DDT. But I still do not understand why updating a different table that the one that the BO was using in the stored procedure did not work.

Thanks for all your help.

Marcia

I do not own the DDT. But I still do not understand why updating a different table that the one that the BO was using in the stored procedure did not work.

Well, I don't really have enough information to diagnose the issue you were having.  I would need to see the settings on the BO and the structure of the table as well.  But at any rate, glad you got it going.  It is BAD mojo to have spaces in fields anyway!!!

Hi Marcia,

Why don't you update the view, directly from the BO?

You should write a TRIGGER INSTEAD OF on that view, and you deal with schemas, grants, and even with spaced names in the tables columns in the code of this trigger.
In some of such views, from within the trigger, I call stored procedures with EXECUTE AS <here, a higher level user>, passing the content of the INSERTED and DELETED tables.