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