0

When passing a data table to stored procedure I get the error

The SqlParameterCollection only accepts non-null SqlParameter type objects, not DataTable objects.

My table type is below

CREATE TYPE [dbo].[TaskNote] AS TABLE(
[TaskId] [bigint] NULL,
[NoteId] [bigint] NULL,
[Note] [nvarchar](500) NULL
)

I have used below code stored procedure

CREATE PROCEDURE [dbo].[UpdateNote]
( 
   @EditedNotes [TaskNote] READONLY
)
AS
BEGIN

    // code
END

and I called this stored procedure like below

DataTable editNotes = new DataTable();
editNotes.Columns.Add("TaskId", typeof(long));
editNotes.Columns.Add("NoteId", typeof(long));
editNotes.Columns.Add("Note", typeof(string));


if(task.EditedNotes.Count > 0)
{
    foreach (var taskNote in task.EditedNotes)
    {
        DataRow newRow = editNotes.NewRow();
        newRow["TaskId"] = taskNote.TaskID;
        newRow["NoteId"] = taskNote.ID;
        newRow["Note"] = taskNote.Note;

        editNotes.Rows.Add(newRow);
    }
}

SqlParameter editedNotesParam = new SqlParameter("@EditedNotes", SqlDbType.Structured);
editedNotesParam.Value = editNotes;
editedNotesParam.TypeName = "TaskNote";

using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("UpdateNote", cnn))
   {
        cnn.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(editNotes);

        var result = cmd.ExecuteScalar();
    }
}

1 Answer 1

1

Your Datatable here is the value of the SqlParameter. You need to add the SqlParamater itself to the Parameters collection,not the value of the SqlParameter.

 cmd.Parameters.Add(editedNotesParam);
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.