Table Valued Parameter - Cannot find data type dbo.typeQuestionSort

Question

Hello I'm running into an error using Table Valued Parameters. I found this nice article here (Article), and it explains how to use it, but when I run the code, I get this error

"Column, parameter, or variable @DT. : Cannot find data type dbo.typeQuestionSort."

I'm not really sure how to fix it. First I created the Data Type in my Database via this code:

CREATE TYPE [dbo].[typeQuestionSort] AS TABLE(
[typeQuestionID] [int] NOT NULL,
[typeSortOrder] [int] NOT NULL
)
GO

That was successfully created. Then in my C# application I have this method to update the sort order of some questions in my application in the Database table:

    protected void UpdateSortOrder()
    {
        DataTable dt = new DataTable("Tim");
        dt.Columns.Add(new DataColumn("typeQuestionID", typeof(int)));
        dt.Columns.Add(new DataColumn("typeSortOrder", typeof(int)));

        DataRow r1 = dt.NewRow();
        r1["typeQuestionID"] = 1;
        r1["typeSortOrder"] = 0;
        dt.Rows.Add(r1);

        try
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                SqlParameter p = new SqlParameter("@DT", SqlDbType.Structured);
                p.Value = dt;
                p.TypeName = "dbo.typeQuestionSort";
                cmd.Parameters.Add(p);
                cmd.CommandType = CommandType.Text;
                cmd.CommandText =
                    "UPDATE tblVQuestions"
                    + " SET [SortOrder] = typeSortOrder"
                    + " FROM @DT"
                    + " WHERE QuestionID = typeQuestionID AND PID = 12 AND SID = 12"
                    + " BEGIN TRY"
                    + " DROP TYPE dbo.typeQuestionSort"
                    + " END TRY"
                    + " BEGIN CATCH"
                    + " END CATCH";
                cmd.ExecuteNonQuery();
            }
        }
        catch
        {

        }

    }

Any help is greatly appreciated.

Answer 1

Credit goes to Damien_The_Unbeliever's comment.

I'm not sure why, but the article has the user dropping the table. As Damien stated, it should be a long lived object. So I re-created the table and removed the drop stuff. Now it works wonderfully.

This:

        SqlConnection conn = new SqlConnection(ConnectionString);
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            SqlParameter p = new SqlParameter("@DT", SqlDbType.Structured);
            p.Value = dt;
            p.TypeName = "dbo.typeQuestionSort";
            cmd.Parameters.Add(p);
            cmd.CommandType = CommandType.Text;
            cmd.CommandText =
                "UPDATE tblVQuestions"
                + " SET [SortOrder] = typeSortOrder"
                + " FROM @DT"
                + " WHERE QuestionID = typeQuestionID AND PID = 12 AND SID = 12"
                + " BEGIN TRY"
                + " DROP TYPE dbo.typeQuestionSort"
                + " END TRY"
                + " BEGIN CATCH"
                + " END CATCH";
            cmd.ExecuteNonQuery();
        }

Becomes This:

        SqlConnection conn = new SqlConnection(ConnectionString);
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            SqlParameter p = new SqlParameter("@DT", SqlDbType.Structured);
            p.Value = dt;
            p.TypeName = "dbo.typeQuestionSort";
            cmd.Parameters.Add(p);
            cmd.CommandType = CommandType.Text;
            cmd.CommandText =
                "UPDATE tblVQuestions"
                + " SET [SortOrder] = typeSortOrder"
                + " FROM @DT"
                + " WHERE QuestionID = typeQuestionID AND PID = 12 AND SID = 12";
            cmd.ExecuteNonQuery();
        }