BUG: SQL Server procs and Schemas

5/12/2006 2:00:00 AM #

Hi, I am trying to use EZObj on a 1 table with one PK field.  I verified the schema generation does recoognize the PK field.  the isInPrimaryKey is set to TRUE. My code is as follow

oSvc.CommandType = Command.Text
oSvc.LoadByPrimaryKey("ICD")
oSvc.DSCP = "NEW"
oSvc.Save()

For some reason, the dynamic generate UPDATE SQL doesnt have the where clause.  It is trying to update all ROWs and I get a DUP KEY error.  What am I doing wrong ?

tranfamily35

5/13/2006 2:00:00 AM #

========================
CREATE TABLE [SERVICE] (
  [CODE] [varchar] (3) NOT NULL ,
  [DSCP] [varchar] (40) NOT NULL ,
  [PRODUCT_CODE] [varchar] (6) NULL ,
  [WGHT] [decimal](5, 2) NULL ,
  [crvl_code] [varchar] (3) NULL ,
  [dscp_abbr] [varchar] (22) NULL ,
  [CMS_REFERRAL_CODE] [varchar] (3) NULL ,
  CONSTRAINT [PK_SERVICE] PRIMARY KEY  CLUSTERED
  (
    [CODE]
  )  ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO SERVICE (CODE, DSCP) VALUES ('IM','Imaging')
GO
===================================

Hi mgnoonan,

I tried to set the AutoKey to true and still no success.  I noticed the dynamic UPDATE SQL generate for commandType.Text has the PK field in the SET clause.  I enhanced

----------------------------------------------------------------------------------
the  _Service.GetUpdateCommand(ByVal commandType As CommandType)
...
Case commandType.Text
    Me.Query.ClearAll()
    For Each item As SchemaItem In Me.SchemaEntries
       If Not (item.IsAutoKey OrElse item.IsComputed) Then
          Me.Query.AddUpdateColumn(item)
       End If
    Next

TO

Case commandType.Text
    Me.Query.ClearAll()
    For Each item As SchemaItem In Me.SchemaEntries
       If Not (item.IsAutoKey OrElse item.IsComputed OrElse item.IsInPrimaryKey) Then
          Me.Query.AddUpdateColumn(item)
       End If
    Next

------------------------------------------------------------------

After adding the "OrElse item.IsInPrimaryKey" into the IF clause, the PK fields are no longer in the UPDATE SET clause.


As for the dynamic UPDATE SQL WHERE clause, I looked in SqlServerDynamicQuery.BuildUpdateQuery()  I do see that it check for isInPrimaryKey property and build the KeyColumns accordngly.  I am not sure why the generated UPDATE SQL doesnt have the where clause

Generated dynamic SQL:
exec sp_executesql N'UPDATE [dbo].[SERVICE] SET [CODE] = @CODE, [DSCP] = @DSCP, [PRODUCT_CODE] = @PRODUCT_CODE, [WGHT] = @WGHT, [crvl_code] = @crvl_code, [dscp_abbr] = @dscp_abbr, [CMS_REFERRAL_CODE] = @CMS_REFERRAL_CODE', N'@CODE nvarchar(4000),@DSCP nvarchar(4000),@PRODUCT_CODE nvarchar(4000),@WGHT decimal(28,0),@Crvl_code nvarchar(4000),@Dscp_abbr nvarchar(4000),@CMS_REFERRAL_CODE nvarchar(4000)', @CODE = N'EI', @DSCP = N'Early Intervention', @PRODUCT_CODE = N'ADV', @WGHT = NULL, @Crvl_code = N'EI', @Dscp_abbr = N'Early Intervention', @CMS_REFERRAL_CODE = N'L11'


Thanks for your help.

James tran

tranfamily35

5/13/2006 2:00:00 AM #

One more thing I would like to add.

I am using the ver1.1.  When I try to run the EZObj src, I keep getting this compile error

C:\EasyObjects11\src\Configuration\Design\DynamicQueryConfigurationDesignManager.cs(120): The type or namespace name 'ConfigurationDictionary' does not exist in the class or namespace 'Microsoft.Practices.EnterpriseLibrary.Configuration' (are you missing an assembly reference?)

on line 120:
public void BuildContext(IServiceProvider serviceProvider, configurationDictionary configurationDictionary)

tranfamily35

5/13/2006 2:00:00 AM #

Please disregard the error "The type or namespace name 'ConfigurationDictionary' does not exist in the class or namespace "  above.  I added the MS Ent Lib to the EasyObject.Configuration.Design project and everything seem good now.

tranfamily35