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