TVP does not conform to table type

Question

Below is the function that inserts my data.

                using (SqlCommand insSwipeDataCommand = connection.CreateCommand())
                {
                    insSwipeDataCommand.Transaction = transaction;
                    insSwipeDataCommand.CommandType = CommandType.StoredProcedure;
                    insSwipeDataCommand.CommandText = "dbo.insSwipeData_sp";

                    SqlParameter attendeeTableParam = insSwipeDataCommand.Parameters.Add("@AttendeeTable", SqlDbType.Structured);
                    attendeeTableParam.Value = this.dataTable;
                    attendeeTableParam.TypeName = "AttendeeTableType";

                    // add orgid parameter
                    insSwipeDataCommand.Parameters.Add("@orgId", SqlDbType.UniqueIdentifier).Value = this.organizationId;
                    insSwipeDataCommand.ExecuteNonQuery();
                }

insSwipeData_sp

create PROC dbo.insSwipeData_sp
(@AttendeeTable         AttendeeTableType READONLY
,@orgId                 UNIQUEIDENTIFIER
)

AS

BEGIN
    SET NOCOUNT ON


    DECLARE @enteredUserId  UNIQUEIDENTIFIER
    SET @enteredUserId = 'xxxxxxxxx-xxxx-xxx-xxxx-xxxxxxxxxx'

    -- Delete old Swipe records
    DELETE FROM dbo.swipeData_tbl
    WHERE orgId = @orgId

    -- CREATE Swipe Records
    INSERT INTO dbo.swipeData_tbl
    (orgId, sdid, rawData, enteredUserId, enteredUtc, manualEntry)
    SELECT @orgId, attendeeId, barcode
          ,@enteredUserId, GETUTCDATE(), 0 -- Consider ( datepart , date ) if date here is needed as NVARCHAR
    FROM @AttendeeTable
    WHERE barcode IS NOT NULL and LTRIM(RTRIM(barcode)) <> '';
END

Here is an image of my AttendeeTableType schema.

and here is an image of my this.datatable that i am using for my attendeeTableParam On the insSwipeDataCommand.ExecuteNonQuery(); line i get the following error.

The data for table-valued parameter "@AttendeeTable" doesn't conform to the table type of the parameter.

Answer 1

Per the error, your data does not conform to the table type exactly. Note "exactly" -- if you do not specify types for the columns, they will be inferred, and they can easily be inferred incorrectly. The best approach here is to create a table that you know matches the table type definition:

var dt = new DataTable();
dt.Columns.Add("firstName", typeof(string)).MaxLength = 100;
dt.Columns.Add("lastName", typeof(string)).MaxLength = 100;
dt.Columns.Add("studentId", typeof(string)).MaxLength = 10;
dt.Columns.Add("email", typeof(string)).MaxLength = 100;
dt.Columns.Add("barcode", typeof(string)).MaxLength = 100;
dt.Columns.Add("dob", typeof(string)).MaxLength = 200;
dt.Columns.Add("major", typeof(string)).MaxLength = 200;
dt.Columns.Add("gender", typeof(string)).MaxLength = 200;
dt.Columns.Add("classCode", typeof(string)).MaxLength = 15;
dt.Columns.Add("currentclassCode", typeof(string)).MaxLength = 15;
dt.Columns.Add("entranceCode", typeof(string)).MaxLength = 15;
dt.Columns.Add("attendeeId", typeof(Guid));

And then use .Clone() to create a new table with the correct schema when you need to insert data. This way, if you have a type or length mismatch, it will be caught on the client end.

There is another approach you can take that does not rely on embedding the table definition into the application, which is fetching it from the database. There are pros and cons to this -- it requires an extra roundtrip to the database and it's not as easy to spot mistakes in the application logic if the types or columns don't match, but it does give you additional flexibility to change the type without having to change the application (adding a new, nullable column, for example).

var dt = new DataTable();
using (var connection = new SqlConnection(...)) {
    connection.Open();
    using (var command = new SqlCommand()) {
        command.Connection = connection;
        command.CommandText = "DECLARE @t dbo.AttendeeTableType; SELECT * FROM @t;"
        using (var reader = command.ExecuteReader()) {
            dt.Load(reader);
        }
    }
}

Obviously you probably want to cache the results of this and .Clone(), rather than doing it for every command involving the table type parameter.