none
Table variable type? RRS feed

  • Question

  • I try the following code:

                DataTable skuTable = new DataTable("SkuList");
                skuTable.Columns.Add("VariantId", typeof(string));
                while (reader.Read())
                {
                  string sku = Convert.ToString(reader.GetValue(0)).Trim();
                  skuTable.Rows.Add(sku);
                }
    . . . . .
                catalogCommand.Parameters.Add("@in_values", SqlDbType.Structured);
                catalogCommand.Parameters["@in_values"].Value = skuTable;
    


    But when I execute this command I get the error:

    System.ArgumentException: The table type parameter '@in_values' must have a valid type name.
    	at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
    	at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
    	at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
    	at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    	at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    	at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    	at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    


    What is a 'valid type name'?

     

     

     


    Kevin Burton
    Monday, July 18, 2011 12:25 AM

All replies

  • Hi Kevin,

    Welcome!

    There may be some misunderstanding of Table-variable. I quote its scope here:

    A table-valued parameter is scoped to the stored procedure, function or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.

    http://www.codeproject.com/KB/database/Load_DataSet_In_SQL_Table.aspx?msg=2795331

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, July 18, 2011 9:01 AM
    Moderator
  • So I am unclear what I am doing wrong. What is a 'valid type name'? And how do I set it?
    Kevin Burton
    Monday, July 18, 2011 3:21 PM
  • Hi Kevin,

    Try to convert DataSet to XML and use Store procedure to insert the XML, you can refer the frist link.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 19, 2011 9:24 AM
    Moderator
  • Thank you.

    I was trying to compare the relative performance of a table variable vs. XML so converting to XML kind of defeats the purpose.


    Kevin Burton
    Tuesday, July 19, 2011 11:39 AM
  • Kevin,

    I believe error message is misleading. You cannot pass DataTable as a parameter's value, since there is no support for DataTables in OLEDB or managed  providers.


    Val Mazur (MVP)

    http://www.xporttools.net

    Friday, July 22, 2011 10:37 AM
    Moderator
  • I am using the code from "SQL Server MVP Deep Dives", page 237. Is this incorrect?
    Kevin Burton
    Monday, July 25, 2011 12:52 PM
  • Hi Kevin,

    I'd like to quote some from the book which I downloaded just now.

    ----------------------------------------------------------------------

    The first step is to create the table
    type and define its structure. This is a persistent database object that you can reuse all
    you want within the database.

    The next step is to create a stored procedure that uses a parameter of the MyTbl
    type. The dkSelectFromTVP procedure takes the TVP as its only parameter and returns
    the contents of the table using a SELECT statement.

    >>>Listing 1 Code to use a DataTable to pass a TVP to a stored procedure

    cmd.Parameters.AddWithValue("@CustomerID", 1);
    SqlParameter param = cmd.Parameters.AddWithValue("@Items", dt);
    <strong>param.TypeName = "dbo.OrderDetailstype";
    param.SqlDbType = SqlDbType.Structured;</strong>
    
    

    ----------------------------------------------------------------------

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by KevinBurton Friday, July 29, 2011 12:46 PM
    • Unmarked as answer by KevinBurton Friday, July 29, 2011 12:46 PM
    Wednesday, July 27, 2011 6:32 AM
    Moderator
  • I am still not clear as to why the code I supplied at the beginning of this thread is invalid.

    Thank you.


    Kevin Burton
    Friday, July 29, 2011 12:47 PM
  • hi,

    The code works on my computer, I think you mayn't set TypeName for your Parameter..

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, July 30, 2011 1:42 PM
    Moderator
  • Would you mind posting your code? The code at the beginning of the thread doesn't work for me. I am definitely setting the type name. In fact that is the line that is generating the error.

     


    Kevin Burton
    Sunday, July 31, 2011 2:05 PM
  • Hi Kevin,

    Thanks for your feedback. would you please feel free ping me here with a sample project to repro your problem.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 9, 2011 12:32 PM
    Moderator