CODEWITHSUNDEEP
asp.netsql-server
Jiju John
Jiju John

Reputation: 821

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

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();
    }
}

Upvotes: 0

Views: 412

Answers (1)

Shikhar Arora
Shikhar Arora

Reputation: 886

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);

Upvotes: 1

Related Questions