No mapping exists from dbtype 30 to a known sqldbtype

Question

I am trying to populate specific datatable values into a table. I am using the table value parameter. While adding the tabletype parameter i am thrown to no mapping exists. Can you find the bug in this part.

Below is my .NET Call

cmdabsSQL = dbabsSQL.CreateCommand()
                    cmdabsSQL.CommandText = "SP_INS_ResponseTable"
                    cmdabsSQL.CommandType = CommandType.StoredProcedure                      
                    cmdabsSQL.Parameters.Clear()

                    With dbabsSQL
                        .AddInParameter(cmdabsSQL, "@SystemName", DbType.String, SourceSysName)
                        .AddInParameter(cmdabsSQL, "@ClientName", DbType.String, ClientName)
                        .AddInParameter(cmdabsSQL, "@TableValueTypeII", SqlDbType.Structured, tablevaluedParam)
                        .AddOutParameter(cmdabsSQL, PARAM_OUT_SQLMSG, DbType.Int32, 100)
                    End With                     

                    intResult = dbBRLASQL.ExecuteNonQuery(cmdBRLASQL)

The tablevaluedParam is decalred like below.

Dim tablevaluedParam As DataTable = dstDataset.Tables(1)

here dstDataset has two datatables and the values in the dstDataset tables are populated by a query.I am just mapping the recieved datatable to the type created. Below is my type

CREATE TYPE TableValueTypeII AS TABLE
(ResultId BIGINT,
AccountId nvarchar(255),
RecStatusDesc varchar(100),
MatchCount int)

And this is my stored proc part

Insert into ResponseTable(SrceSysNm,ClntId,GrpId,PrvdId,FileNm,AccId,RestId,
RespSendIn,CreatTs,LstUpdtTs,UserId)
select @SystemName,@ClientName,null,null,null,AccountId,ResultId,0,
GETDATE(),GETDATE(),'Batch' from @TableValueTypeII

The dataTable is in the same arrangement of the type created.

Answer 1

You could create dynamic sql like below:

declare @SQL nvarchar(4000)

set @SQL = N'Insert into ResponseTable(SrceSysNm,ClntId,GrpId,PrvdId,FileNm,AccId,RestId,
RespSendIn,CreatTs,LstUpdtTs,UserId)
select '+ @SystemName+','+@ClientName+',null,null,null,AccountId,ResultId,0,
GETDATE(),GETDATE(),''Batch'' from '+ @TableValueTypeII

EXEC sp_executesql @SQL;

Then pass the table name as a simple string value.

Note: Please check for single quotes while forming dynamic sql

Answer 2

Just stumbled the same problem. Solution was simple dbabsSQL should be SqlDatabase explicitly instead of

Dim db = DatabaseFactory.CreateDatabase()

use

Dim db As SqlDatabase = DatabaseFactory.CreateDatabase()

Answer 3

try doing it this way , worked perfectly for me ,

    Dim _DataReader As SqlDataReader = Nothing
    Dim SQLCon As New SqlClient.SqlConnection("Your connection string")
    SQLCon.Open()
    Dim SQLCom = New SqlCommand

    With SQLCom
        .Connection = SQLCon
        .CommandText = "SP_INS_ResponseTable"
        .CommandType = CommandType.StoredProcedure

        Dim _Param1 As New SqlParameter("@SystemName", SqlDbType.Varchar)
        Dim _Param2 As New SqlParameter("@ClientName", SqlDbType.Varchar)
        Dim _Param3 As New SqlParameter("@TableValueTypeII", SqlDbType.Structured)
        Dim _Param4 As New SqlParameter(PARAM_OUT_SQLMSG, SqlDbType.Int32)          

        //pass the parameters values here 
        _Param1.Value = _SystemName
        _Param2.Value = _ClientName
        _Param3.Value = _TableValueTypeII
        _Param4.Value = _PARAM_OUT_SQLMSG

        .Parameters.Add(_Param1)
        .Parameters.Add(_Param2).
        .Parameters.Add(_Param3)
        .Parameters.Add(_Param4)    
    End With

    _DataReader = SQLCom.ExecuteReader()

    While (_DataReader.Read)
        Dim _Record As New (your own returned data type)

        With _Record 
            .SystemName = (_DataReader(0)) // make sure they are not null(_DataReader(x)) assign it to empty string if so
            .ClientName = (_DataReader(1))
            .TableValueTypeII = (_DataReader(2))
            .PARAM_OUT_SQLMSG = (_DataReader(3))
        End With
        return _Record
    End While

    SQLCon.Close()
    SQLCon.Dispose()
    SQLCom.Dispose()