Operand type clash nvarchar

David Chase 681 Reputation points
2020-12-29T16:59:42.13+00:00

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
SQL Server | Other
{count} votes

Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2020-12-31T14:49:59.74+00:00

    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
    

10 additional answers

Sort by: Most helpful
  1. 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


  2. David Chase 681 Reputation points
    2020-12-30T14:13:22.233+00:00

    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
    
    0 comments No comments

  3. 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.

    0 comments No comments

  4. David Chase 681 Reputation points
    2020-12-30T22:03:13.813+00:00

    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
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.