Operand type clash nvarchar

David Chase 681

Testing our web app after column encryption is giving us the error below when we try to update an encrypted table. The error message refers to nvarchar(4000) being incompatible with nvarchar(20). In the table being updated we do not have an nvarchar(4000) column so it is confusing.

"Operand type clash: nvarchar(4000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'kdctest') is incompatible with nvarchar(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'kdctest')"

Below is the scema of table being updated.

CREATE TABLE [dbo].[tblPatients](
    [PatientID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](20) NULL,
    [MiddleName] [nvarchar](20) NULL,
    [LastName] [nvarchar](30) NULL,
    [DOB] [smalldatetime] NOT NULL,
    [StatusID] [smallint] NOT NULL,
    [DietID] [int] NOT NULL,
    [StartDiet] [smalldatetime] NULL,
    [EndDiet] [smalldatetime] NULL,
    [Email] [nvarchar](100) NULL,
    [OrgID] [int] NOT NULL,
    [Gender] [char](1) NULL,
    [CaregiverLogin] [nvarchar](20) NULL,
    [CaregiverPW] [nvarchar](20) NULL,
    [LastLogin] [smalldatetime] NULL,
    [MedRecordNo] [varchar](100) NULL,
 CONSTRAINT [PK_tblPatients] PRIMARY KEY NONCLUSTERED 
(
    [PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPatients] ADD  CONSTRAINT [DF_tblPatients_StatusID]  DEFAULT (1) FOR [StatusID]
GO

ALTER TABLE [dbo].[tblPatients] ADD  CONSTRAINT [DF_tblPatients_DietID]  DEFAULT (0) FOR [DietID]
GO

ALTER TABLE [dbo].[tblPatients] ADD  CONSTRAINT [DF_tblPatients_UserID]  DEFAULT (0) FOR [OrgID]
GO

And finally below is the update SQL.

ALTER PROCEDURE [dbo].[kd_updtblPatients]
    @PatientID          int,
    @FirstName          nvarchar(20),
    @MiddleName         nvarchar(20),
    @LastName           nvarchar(30),
    @DOB                smalldatetime,
    @StartDiet          smalldatetime,
    @EndDiet            smalldatetime,
    @Email              nvarchar(100),
    @Gender             char(1),
    @CaregiverLogin     nvarchar(50),
    @CaregiverPW        nvarchar(20),
    @DietDiscontinued   bit,
    @UserLoginID        int,
    @MedRecordNo        varchar(100)

AS 
    SET NOCOUNT ON 

    BEGIN

        --Put login userid into session context for use in trigger(s)
        EXEC sp_set_session_context 'USERID', @UserLoginID, @read_only = 0;

        DECLARE @StatusIDint smallint;
        IF @DietDiscontinued = 1
            BEGIN
                SET @StatusIDint = 0;
            END
        ELSE
            BEGIN
                SET @StatusIDint = 1;
            END

        UPDATE [dbo].[tblPatients]
        SET [FirstName] = @FirstName, 
            [MiddleName] = @MiddleName, 
            [LastName] = @LastName, 
            [DOB] = @DOB, 
            [StartDiet] = @StartDiet, 
            [EndDiet] = @EndDiet, 
            [Email] = @Email,
            [Gender] = @Gender, 
            [StatusID] = @StatusIDint,
            [MedRecordNo] = @MedRecordNo
        WHERE ([PatientID] = @PatientID)
        AND  NOT EXISTS (SELECT PatientID, FirstName, MiddleName, LastName, DOB, StartDiet,
                                EndDiet, Gender, StatusID, MedRecordNo
                        INTERSECT
                        SELECT @PatientID, @FirstName, @MiddleName, @LastName, @DOB, @StartDiet,
                                @EndDiet, @Gender, @StatusIDint, @MedRecordNo);

        IF EXISTS(SELECT PatientID FROM dbo.tblOrgLogins WHERE PatientID = @PatientID)
            BEGIN
                IF @CaregiverLogin IS NULL AND @CaregiverPW IS NULL
                    BEGIN
                        DELETE FROM dbo.tblOrgLogins WHERE (PatientID = @PatientID);
                    END
                ELSE
                    BEGIN
                        UPDATE [dbo].[tblOrgLogins]
                            SET LogUserName = @CaregiverLogin,
                                LogUserPW = @CaregiverPW,
                                LogUserEmail = @Email
                            WHERE (PatientID = @PatientID)
                            AND NOT EXISTS (SELECT LogUserName, LogUserPW, LogUserEmail
                                            INTERSECT
                                            SELECT @CaregiverLogin, @CaregiverPW, @Email);

                    END
            END
        ELSE
            BEGIN
                IF @CaregiverLogin IS NOT NULL
                    BEGIN
                        INSERT INTO [dbo].[tblOrgLogins]
                               ([OrgID]
                               ,[LogUserName]
                               ,[LogUserPW]
                               ,[LogUserStatus]
                               ,[PatientID]
                               ,[Active]
                               ,[LogUserEmail])
                        SELECT [OrgID]
                              ,@CaregiverLogin
                              ,@CaregiverPW
                              ,10
                              ,@PatientID
                              ,1
                              ,@Email
                          FROM [dbo].[tblPatients]
                        WHERE (PatientID = @PatientID);

                    END
            END


    END

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-12-29T22:34:43.8+00:00

The CREATE TABLE statement does not tell us which columns that are encrypted. It seems that you have scripted an encrypted version.

The procedure inserts data into two tables. Could the problem be with tblOrgLogin? I also seem to recall that you have a trigger. Could the problem be there?

The full error message should have procedure name and line number. That is important information.
David Chase 681 Reputation points

2020-12-29T23:38:16.617+00:00

I disabled the trigger and bypassed everything after the UPDATE statement for tblPatients and got the same error.
AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff

2020-12-30T09:45:25.497+00:00

Hi DavidChase-4918,
Could you please share us more details about error message and encrypted column?
And please refer to this similar thread which might help.
Best Regards,
Amelia
David Chase 681 Reputation points

2020-12-30T14:18:03.87+00:00

The thread you referred to was not applicable as my problem is simply trying to update an encrypted column. As mentioned I was able to disable trigger and just run the UPDATE and got the same error.
Stefan Dascalu 0 Reputation points

2023-04-25T08:56:03.7133333+00:00
Had the same issue and fixed it by changing the way the parameter is added. In my case the encrypted parameter ended up being null so apparently the object was misidentified as some very large string. So when param is null instead of using

cmd.Parameters.AddWithValue(paramName, DBNull.Value);

I ended up using this:

var param = new SqlParameter(paramName, SqlDbType.NVarChar, 1) { Value = DBNull.Value }; cmd.Parameters.Add(param);

I realized the issue was not linked at all to SQL when I opened up the profiler and the statements worked just fine on their own when executed from SSMS.
TWats 5 Reputation points

2023-07-12T20:06:58.74+00:00

The issue I had was the size of the VARCHAR/NVARCHAR was too big compared to what the encrypted column had.
EG:
The encrypted column is an NVARCHAR(100) but the variable was declared as too large, even though the data inserted was within the sized needed.
DECLARE @ToBeEncrypted NVARCHAR(200) = 'test' -- FAILED
DECLARE @ToBeEncrypted NVARCHAR(100) = 'test' -- WORKED

Accepted answer

Erland Sommarskog 121.9K MVP Volunteer Moderator

Same exact error, see new code below.

I don't think so, and I will tell you why in a second.

I took your table and created it in a database where I already had an encryption key.

I then took you stored procedure and created shortened version of it in my database:

CREATE PROCEDURE [dbo].[kd_updtblPatients]
    @PatientID            int,
    @FirstName            nvarchar(20),
    @MiddleName            nvarchar(20),
    @LastName            nvarchar(30),
    @DOB                smalldatetime,
    @StartDiet            smalldatetime,
    @EndDiet            smalldatetime,
    @Email                nvarchar(100),
    @Gender                char(1),
    @CaregiverLogin        nvarchar(50),
    @CaregiverPW        nvarchar(20),
    @DietDiscontinued    bit,
    @UserLoginID        int,
    @MedRecordNo        varchar(100)

AS 
    SET NOCOUNT ON 

    BEGIN

        --Put login userid into session context for use in trigger(s)
        EXEC sp_set_session_context 'USERID', @UserLoginID, @read_only = 0;

        DECLARE @StatusIDint smallint;
        IF @DietDiscontinued = 1
            BEGIN
                SET @StatusIDint = 0;
            END
        ELSE
            BEGIN
                SET @StatusIDint = 1;
            END

        UPDATE [dbo].[tblPatients]
        SET [FirstName] = @FirstName, 
            [MiddleName] = @MiddleName, 
            [LastName] = @LastName, 
            [DOB] = @DOB, 
            [StartDiet] = @StartDiet, 
            [EndDiet] = @EndDiet, 
            [Email] = @Email,
            [Gender] = @Gender, 
            [StatusID] = @StatusIDint,
            [MedRecordNo] = @MedRecordNo
        WHERE ([PatientID] = @PatientID)
        AND  NOT EXISTS (SELECT PatientID, FirstName, MiddleName, LastName, DOB, StartDiet,
                                EndDiet, Gender, StatusID, MedRecordNo
                        INTERSECT
                        SELECT @PatientID, @FirstName, @MiddleName, @LastName, @DOB, @StartDiet,
                                @EndDiet, @Gender, @StatusIDint, @MedRecordNo);


    END

Then I took your VB code and stripped it so that all input comes from constants (since I don't have the rest of your application). When doing this, I noticed that it was calling ms_updtblPatients, rather than kd_updtblPatients, so I changed the name.

Once I had running, I did indeed get an error message about encryption mismatch. Similar, but not exactly the same as you posted above:

Unhandled Exception: System.Data.SqlClient.SqlException: Operand type clash: nvarchar(100) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'slasketti') is incompatible with varchar(100) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'slasketti') collation_name = 'Finnish_Swedish_CS_AS'

No matter your exact trade is as a developer, reading error message carefully is a key skill.

As I had noticed already when I made some initial tests from SSMS that MedRecordNo is varchar and not nvarchar, I could quickly fix the issue, and I was now able to update the test patient I had added. My test procedure is below.

By the way, I get a little worried by CaregiverPW. That sounds awfully much like a password column, and you don't encrypt that one. Although, ideally, it should not be encrypted, but be a salted hash. But you normally not store that in nvarchar(20).

Imports System.Data
Imports System.Data.SqlClient

Module EncryptTest

Public Sub Main()

   Dim connString As String = "Server=.;Database=slasketti;Integrated Security=SSPI;Column Encryption Setting=enabled"
   Using conData As SqlConnection = New SqlConnection(connString)
       Dim cmd As SqlCommand = New SqlCommand()
       cmd.CommandText = "kd_updtblPatients"
       cmd.CommandType = Data.CommandType.StoredProcedure
       cmd.Connection = conData

       Dim parameter As New SqlParameter()
       parameter.ParameterName = "@PatientID"
       parameter.SqlDbType = Data.SqlDbType.Int
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = 1
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@FirstName"
       parameter.SqlDbType = Data.SqlDbType.NVarChar
       parameter.Size = 20
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = "Hans"
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@MiddleName"
       parameter.SqlDbType = Data.SqlDbType.NVarChar
       parameter.Size = 20
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = "Hasse"
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@LastName"
       parameter.SqlDbType = Data.SqlDbType.NVarChar
       parameter.Size = 30
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = "Hansson"
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@DOB"
       parameter.SqlDbType = Data.SqlDbType.SmallDateTime
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = Convert.ToDateTime("1976-02-08")
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@Gender"
       parameter.SqlDbType = Data.SqlDbType.Char
       parameter.Size = 1
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = "M"
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@StartDiet"
       parameter.SqlDbType = Data.SqlDbType.SmallDateTime
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = Convert.ToDateTime("2020-12-08") 
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@EndDiet"
       parameter.SqlDbType = Data.SqlDbType.DateTime
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = Convert.ToDateTime("2020-12-18") 
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@Email"
       parameter.SqlDbType = Data.SqlDbType.NVarChar
       parameter.Size = 100
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = "******@hansson.se"
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@CaregiverLogin"
       parameter.SqlDbType = Data.SqlDbType.NVarChar
       parameter.Size = 20
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = System.DBNull.Value
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@CaregiverPW"
       parameter.SqlDbType = Data.SqlDbType.NVarChar
       parameter.Size = 20
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = System.DBNull.Value
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@MedRecordNo"
       parameter.SqlDbType = Data.SqlDbType.VarChar
       parameter.Size = 100
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = "Hasses recordno"
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@DietDiscontinued"
       parameter.SqlDbType = Data.SqlDbType.Bit
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = 1
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter()
       parameter.ParameterName = "@UserLoginID"
       parameter.SqlDbType = Data.SqlDbType.Int
       parameter.Direction = Data.ParameterDirection.Input
       parameter.Value = 5
       cmd.Parameters.Add(parameter)

       conData.Open()

       cmd.ExecuteScalar()

   End Using
End Sub

End Module

David Chase 681 Reputation points

2020-12-31T17:31:54.813+00:00

The CaregiverPW and CaregiverLogin are no longer used on this table and have been moved to a different table with encryption. Also, I DO read error messages and that is what I found a problem when the error referred to "datetime" when all date columns are smalldatetime.

Thanks for catching the @MedRecordNo issue. I will continue to test again until all issues are resolved.
David Chase 681 Reputation points

2020-12-31T17:47:28.597+00:00

Testing again today I still get the error below which I copied from the error log.

Message: Operand type clash: datetime encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'kdctest') is incompatible with smalldatetime encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'kdctest')
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-12-31T18:41:47.09+00:00

I can only recommend that you work with the example I posted. I can't comment on code I don't see. As I noted, the VB code you posted refers to a different procedure from you share here.

Also, I stripped the procedure to posted to simplify things. You may need to do similar to narrow things down.

And don't forget to collect the line number for the error message.
Stefan Dascalu 0 Reputation points

2023-04-25T08:37:59.3866667+00:00

Comments about not reading the error carefully doesn't read original error. Having the same issue and lost time reading this whole thread only to find out some rando changed question of the thread.

10 additional answers

David Chase 681 Reputation points

2020-12-29T23:23:57.627+00:00

Below is the error that comes back to our asp.net app:

Stack Trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTdsWithTransparentParameterEncryption(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest, Task describeParameterEncryptionTask) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation)
at System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback)
at System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation)
at System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Host = ketodietcalculator.org
Please sign in to rate this answer.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-12-30T07:43:10.087+00:00

You need a better exception handler, so that you get line number and procedure name. This information is available in the exception object. Alternatively, you can set up the Extended Events session that I describe here: http://www.sommarskog.se/Short%20Stories/trace-exceptions.html
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

David Chase 681

Here is the table schema from encrypted table.

CREATE TABLE [dbo].[tblPatients](
    [PatientID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [MiddleName] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [LastName] [nvarchar](30) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [DOB] [smalldatetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    [StatusID] [smallint] NOT NULL,
    [DietID] [int] NOT NULL,
    [StartDiet] [smalldatetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [EndDiet] [smalldatetime] NULL,
    [Email] [nvarchar](100) NULL,
    [OrgID] [int] NOT NULL,
    [Gender] [char](1) NULL,
    [CaregiverLogin] [nvarchar](20) NULL,
    [CaregiverPW] [nvarchar](20) NULL,
    [LastLogin] [smalldatetime] NULL,
    [MedRecordNo] [varchar](100) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
 CONSTRAINT [PK_tblPatients] PRIMARY KEY NONCLUSTERED 
(
    [PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPatients] ADD  CONSTRAINT [DF_tblPatients_StatusID]  DEFAULT ((1)) FOR [StatusID]
GO

ALTER TABLE [dbo].[tblPatients] ADD  CONSTRAINT [DF_tblPatients_DietID]  DEFAULT ((0)) FOR [DietID]
GO

ALTER TABLE [dbo].[tblPatients] ADD  CONSTRAINT [DF_tblPatients_UserID]  DEFAULT ((0)) FOR [OrgID]
GO

David Chase 681 Reputation points

2020-12-30T21:21:08.597+00:00

I had to use asp.net code to send parameters rather than use the FormView standard update procedure.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

David Chase 681

New error below.

Message: Operand type clash: datetime encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'kdctest') is incompatible with smalldatetime encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'kdctest')

Below is the asp.net code I am using to update. I only show date fields because that is what error is stating.

            Using conData As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("KetodataConnectionString").ConnectionString)

                Dim strText As String = ""
                Dim row As FormViewRow = fvPatient.Row
                Dim lbl As Label = CType(row.FindControl("LblPatientID"), Label)
                Dim tx As TextBox = CType(row.FindControl("txtFirstName"), TextBox)
                Dim dtDate As DateTime
                Dim intUserLoginID As Int32 = Convert.ToInt32(Request.Cookies("LogUserID").Value)

                Dim cmd As SqlCommand = New SqlCommand()
                cmd.CommandText = "ms_updtblPatients"
                cmd.CommandType = Data.CommandType.StoredProcedure
                cmd.Connection = conData

                Dim parameter As New SqlParameter()
                parameter.ParameterName = "@PatientID"
                parameter.SqlDbType = Data.SqlDbType.Int
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = lbl.Text
                cmd.Parameters.Add(parameter)

                parameter = New SqlParameter()
                parameter.ParameterName = "@FirstName"
                parameter.SqlDbType = Data.SqlDbType.NVarChar
                parameter.Size = 20
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = tx.Text
                cmd.Parameters.Add(parameter)

                tx = CType(row.FindControl("txtMiddleName"), TextBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@MiddleName"
                parameter.SqlDbType = Data.SqlDbType.NVarChar
                parameter.Size = 20
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = tx.Text
                cmd.Parameters.Add(parameter)

                tx = CType(row.FindControl("txtLastName"), TextBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@LastName"
                parameter.SqlDbType = Data.SqlDbType.NVarChar
                parameter.Size = 30
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = tx.Text
                cmd.Parameters.Add(parameter)

                Dim ddl As DropDownList = CType(row.FindControl("ddlDOBMonth"), DropDownList)
                Dim ddlday As DropDownList = CType(row.FindControl("ddlDOBDay"), DropDownList)
                tx = CType(row.FindControl("txtDOByear"), TextBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@DOB"
                parameter.SqlDbType = Data.SqlDbType.DateTime
                parameter.Direction = Data.ParameterDirection.Input
                dtDate = Convert.ToDateTime(ddl.SelectedValue & "-" & ddlday.SelectedValue & "-" & tx.Text)
                parameter.Value = dtDate
                cmd.Parameters.Add(parameter)

                Dim rbl As RadioButtonList = CType(row.FindControl("rblGender"), RadioButtonList)
                parameter = New SqlParameter()
                parameter.ParameterName = "@Gender"
                parameter.SqlDbType = Data.SqlDbType.Char
                parameter.Size = 1
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = rbl.SelectedValue
                cmd.Parameters.Add(parameter)

                tx = CType(row.FindControl("txtStartDiet"), TextBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@StartDiet"
                parameter.SqlDbType = Data.SqlDbType.DateTime
                parameter.Direction = Data.ParameterDirection.Input
                If tx.Text <> "" Then
                    parameter.Value = tx.Text
                Else
                    parameter.Value = vbNull
                End If
                cmd.Parameters.Add(parameter)

                tx = CType(row.FindControl("txtEndDiet"), TextBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@EndDiet"
                parameter.SqlDbType = Data.SqlDbType.DateTime
                parameter.Direction = Data.ParameterDirection.Input
                If tx.Text <> "" Then
                    parameter.Value = tx.Text
                Else
                    parameter.Value = vbNull
                End If
                cmd.Parameters.Add(parameter)

                tx = CType(row.FindControl("txtEmail"), TextBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@Email"
                parameter.SqlDbType = Data.SqlDbType.NVarChar
                parameter.Size = 100
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = tx.Text
                cmd.Parameters.Add(parameter)

                tx = CType(row.FindControl("txtCaregiverLogin"), TextBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@CaregiverLogin"
                parameter.SqlDbType = Data.SqlDbType.NVarChar
                parameter.Size = 20
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = tx.Text
                cmd.Parameters.Add(parameter)

                tx = CType(row.FindControl("txtCaregiverPW"), TextBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@CaregiverPW"
                parameter.SqlDbType = Data.SqlDbType.NVarChar
                parameter.Size = 20
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = tx.Text
                cmd.Parameters.Add(parameter)

                tx = CType(row.FindControl("txtMedRecordNo"), TextBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@MedRecordNo"
                parameter.SqlDbType = Data.SqlDbType.NVarChar
                parameter.Size = 100
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = tx.Text
                cmd.Parameters.Add(parameter)

                Dim ck As CheckBox = CType(row.FindControl("ckDietDiscontinued"), CheckBox)
                parameter = New SqlParameter()
                parameter.ParameterName = "@DietDiscontinued"
                parameter.SqlDbType = Data.SqlDbType.Bit
                parameter.Direction = Data.ParameterDirection.Input
                If ck.Checked Then
                    parameter.Value = 1
                Else
                    parameter.Value = 0
                End If
                cmd.Parameters.Add(parameter)

                parameter = New SqlParameter()
                parameter.ParameterName = "@UserLoginID"
                parameter.SqlDbType = Data.SqlDbType.Int
                parameter.Direction = Data.ParameterDirection.Input
                parameter.Value = intUserLoginID
                cmd.Parameters.Add(parameter)

                conData.Open()

                cmd.ExecuteScalar()

            End Using

Share via

Operand type clash nvarchar

10 additional answers

Your answer