0

I am currently trying to pass a two table value parameters from a .net application to a database.

The error that I am getting is

DBNull value for parameter '@StageNotes' is not supported. Table-valued parameters cannot be DBNull.

I have checked that the parameter value is not null when I pass it, and the error that I get is not a sqlException, rather just a general Exception that passes this error back

The parameters are declared like so

Dim stageNotesparamTable2SQL As New SqlParameter("@StageNotes", SqlDbType.Structured)
Dim responsesParamTable2SQL As New SqlParameter("@Responses", SqlDbType.Structured)

and the values are assigned like this

Dim responseTable = ResponsesToDt()
responsesParamTable2SQL.Value = responseTable

Dim stageTable = StageNotesToDt()
stageNotesparamTable2SQL.Value = stageTable

The parameters are declared in the stored proc like this

@StageNotes                 [App].[StageNotesTableType] READONLY,
@Responses                  [app].QuestionResponseTableType READONLY,

When debugging I see that the stageNotesparamTable2SQL.Value is showing that there is a datatable in there and has the data so it is definitely not null.

I would be grateful if anyone could help me with this.

thanks

-- edit ---

    Protected Function ResponsesToDt() As DataTable
    Dim dt As New DataTable()


    dt.Columns.Add("CheckId", GetType(Integer))
    dt.Columns.Add("QuestionId", GetType(Integer))
    dt.Columns.Add("AnswerId", GetType(Integer))
    dt.Columns.Add("StaffNumber", GetType(String)).MaxLength = 15

    Dim dictValues = _dicOfControlsAndValues.Where(Function(x) x.Key.Contains("ddl_Stage_"))

    For Each item In dictValues

        Dim ddlIdBreakDown = item.Key.ToString().Split("_").ToList

        Dim checkid As Integer = item.Key.ToString().Split("_").Last
        Dim questionId As Integer = Convert.ToInt16(ddlIdBreakDown(4))
        Dim answerId As Integer = Convert.ToInt16(item.Value)
        Dim staffNumber As String = GetLoggedOnStaffNumber()

        dt.Rows.Add(checkid, questionId, answerId, staffNumber)
    Next

    Return dt
End Function

 Protected Function StageNotesToDt() As DataTable

    Dim dt As New DataTable

    dt.Columns.Add("CheckId", GetType(Integer))
    dt.Columns.Add("StageId", GetType(Integer))
    dt.Columns.Add("StageNotes", GetType(String))
    dt.Columns.Add("ModifiedDate", GetType(String))

    Dim dictValues = _dicOfControlsAndValues.Where(Function(x) x.Key.Contains("textArea_Stage"))

    For Each item In dictValues
        Dim stageNote As String = String.Empty
        Dim ddlIdBreakDown = item.Key.ToString().Split("_").ToList
        If String.IsNullOrEmpty(item.Value.ToString()) Then
            stageNote = "."
        Else
            stageNote = item.Value.ToString()
        End If
        Dim checkid As Integer = item.Key.ToString().Split("_").Last
        Dim stageId As Integer = Convert.ToInt16(ddlIdBreakDown(2))
        Dim stageNotes As String = stageNote
        Dim modifiedDate As DateTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")

        dt.Rows.Add(checkid, stageId, stageNotes, modifiedDate)
    Next

    Return dt

End Function

-- edit 2 --

 DAL.ExecCommand("[App].[usp_SaveCheckDetails]", "",
                                                checkIdParam,
                                                userIdParam,
                                                caseNoteParam,
                                                checkNoteParam,
                                                checkCompletedParam,
                                                CheckFeedbackSentToUserId,
                                                stageNotesparamTable2SQL,
                                                responsesParamTable2SQL)

Then the DAL im having to call.

The DAL is a shared repo that Im not able to edit at the moment:

Public Function ExecCommand(ByVal spName As String, ByVal connStringName As String, ByVal ParamArray SPParms() As SqlClient.SqlParameter) As Boolean

    Dim SqlConnection As New SqlConnection
    Dim sqlCommand As New SqlClient.SqlCommand
    Dim returnCode As Boolean = False

    Try

        If connStringName <> "" Then
            SqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings(connStringName).ConnectionString
        Else
            SqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
        End If

        sqlCommand.CommandType = CommandType.StoredProcedure
        sqlCommand.CommandText = spName
        For Each p As SqlParameter In SPParms
            If p.Direction <> ParameterDirection.Output Then
                If Not p.Value.GetType() = GetType(Integer) Then
                    If p.Value.ToString() = "" OrElse p.Value = Nothing Then
                        p.Value = DBNull.Value
                    End If
                End If
            End If

            sqlCommand.Parameters.Add(p)
        Next
        sqlCommand.Connection = SqlConnection
        SqlConnection.Open()
        sqlCommand.ExecuteNonQuery()
        returnCode = True

    Catch sqlEx As SqlException
        HelpersErrorHandling.WriteAppLogEntry(Me.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().ToString, sqlEx)
    Catch ex As Exception
        If Not ex.Message.ToString().Contains("Thread was being aborted") Then
            HelpersErrorHandling.WriteAppLogEntry(Me.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().ToString, ex)
        End If

    Finally

        Try
            sqlCommand.Parameters.Clear()
            sqlCommand.Dispose()
            SqlConnection.Close()
            SqlConnection.Dispose()

        Catch ex As Exception
            HelpersErrorHandling.WriteAppLogEntry(Me.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().ToString, ex)

        End Try

    End Try

    Return returnCode

End Function
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Simon Price
  • 3,011
  • 3
  • 34
  • 98
  • What does the methods `ResponsesToDt()` and `StageNotesToDt()` returns? – Zohar Peled Feb 24 '16 at 13:00
  • they both return data tables, I have edited the code to show the return values – Simon Price Feb 24 '16 at 13:03
  • The parameters themselves seems OK to me. Can you show the code that adds these parameters to the command object? – Zohar Peled Feb 24 '16 at 13:05
  • added that aspect in now – Simon Price Feb 24 '16 at 13:08
  • Seems like your dal is filtering out the data tables, though I'm not sure why. put a breakpoint on `For Each p As SqlParameter In SPParms` and try to see what happens. – Zohar Peled Feb 24 '16 at 14:13
  • Something looks fishy in that part of the DAL you show: an empty string (you know it is empty) is usually seen as different from a null string (you have no idea what it could be), however those two possibilities are both stored as DBNull.Value. – Andrew Morton Feb 24 '16 at 16:02
  • Please show your CREATE PROCEDURE SQL statement – Steve Ford Feb 24 '16 at 17:17
  • it would seem that the person who coded this DAL is stripping out the table if any of the cells are null. As a result I have had the argument to amend the DAL and add a new function in that will take an additional variable and not strip out the null values. Thank you for your time and comments – Simon Price Feb 25 '16 at 06:51

0 Answers0