I'm new to TVP in SQL Server and trying to understand the basics. I created a sample TVP in the Northwind database in SQL Express. My code from VB.NET is fairly simple (see below). The TVP parameter is empty in the procedure. I've tested the TVP in SQL Server and it works fine. Any ideas? Does SQL Express fully support table value parameters?
Dim dt As DataTable = New DataTable()
dt.Columns.Add("RegionID", GetType(Int64))
dt.Columns.Add("RegionDesc", GetType(String))
dt.Rows.Add(21, "FromProgram2")
dt.Rows.Add(22, "FromProgram3")
Try
If dt.Rows.Count > 0 Then
Dim mycommand As SqlCommand = New SqlCommand
Dim myconn As SqlConnection = New SqlConnection(connstring)
mycommand.CommandText = "dbo.TestType"
mycommand.Connection = myconn
Dim param1 As SqlParameter = mycommand.Parameters.AddWithValue("@RegionData", dt)
param1.SqlDbType = SqlDbType.Structured
param1.TypeName = "dbo.RegionType"
mycommand.Connection.Open()
mycommand.ExecuteNonQuery()
mycommand.Connection.Close()
Console.WriteLine("")
Console.ReadLine()
Else
Console.Write("No data in datatable")
Console.ReadLine()
End If
Catch ex As Exception
Console.WriteLine(ex.Message)
Console.ReadLine()
End Try