Insert data from ListView into table valued parameter in vb.net

Question

I have this code in SQL Server 2008, but I try to insert data from my vb.net application - how can I do that?

CREATE procedure [dbo].[PO_INSERT_WITH_LINE_ITEM]  
    (@PO_ID smallint = null output,
     @SUPPLIER_ID smallint,
     @CREATED_BY smallint,
     @CREATION_DATE date,
     @POD dbo.POD_UDT READONLY)
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON

    BEGIN TRAN;

    INSERT INTO dbo.tbl_purchases_order (SUPPLIER_ID, CREATED_BY, CREATION_DATE, PO_TOTAL)
        SELECT 
            @SUPPLIER_ID, @CREATED_BY, @CREATION_DATE,
            PO_TOTAL = SUM(TOTAL_COST)
        FROM 
            @POD i;

    SELECT @PO_ID = CONVERT(SMALLINT, SCOPE_IDENTITY());

    INSERT INTO dbo.tbl_pucheases_order_detail (PO_ID,PRODUCT_ID, DISCOUNT, UNIT_COST, QTY, TOTAL_BEFORE_DISCOUNT, DISCOUNTED_AMOUNT, TOTAL_COST)
        SELECT 
            @PO_ID, PRODUCT_ID, DISCOUNT, UNIT_COST, QTY,
            TOTAL_BEFORE_DISCOUNT, DISCOUNTED_AMOUNT, TOTAL_COST
        FROM 
            @POD i;

    COMMIT TRAN;
END;

So can I insert multiple rows from ListView in Vb.net?

Thank

No, you cant pass a VB.net ListView as a tvp using a UDTT. Maybe as an XML. Post VB.net code also. — Horaciux, Dec 31 '17 at 01:37
https://www.codeproject.com/Articles/1073184/How-to-import-data-into-MS-SQL-server-from-CSV-and has an example of an XML import. (Not the XML BULKINSERT, but below that.) — NeedsAnswers, Dec 31 '17 at 05:32

score 1 · Answer 1 · answered Dec 31 '17 at 12:53

According to the documentation, you can pass a TVP as a DataTable, DbDataReader or IEnumerable<SqlDataRecord>. Below is a DataTable example which adds a row for each list view item and a column for each sub item. Change the types to match your actual table types.

Dim dt = New DataTable
dt.Columns.Add("PO_ID", GetType(Int32))
dt.Columns.Add("PRODUCT_ID", GetType(Int32))
dt.Columns.Add("DISCOUNT", GetType(Decimal))
dt.Columns.Add("UNIT_COST", GetType(Decimal))
dt.Columns.Add("QTY", GetType(Int32))
dt.Columns.Add("TOTAL_BEFORE_DISCOUNT", GetType(Decimal))
dt.Columns.Add("DISCOUNTED_AMOUNT", GetType(Decimal))
dt.Columns.Add("TOTAL_COST", GetType(Decimal))
Dim columnValues(dt.Columns.Count - 1) As Object

For Each lvi As ListViewItem In ListView1.Items
    columnValues(0) = Int32.Parse(lvi.SubItems(0).Text)
    columnValues(1) = Int32.Parse(lvi.SubItems(1).Text)
    columnValues(2) = Decimal.Parse(lvi.SubItems(2).Text)
    columnValues(3) = Decimal.Parse(lvi.SubItems(3).Text)
    columnValues(4) = Int32.Parse(lvi.SubItems(4).Text)
    columnValues(5) = Decimal.Parse(lvi.SubItems(5).Text)
    columnValues(6) = Decimal.Parse(lvi.SubItems(6).Text)
    columnValues(7) = Decimal.Parse(lvi.SubItems(7).Text)
    dt.Rows.Add(columnValues)
Next

Using connection As New SqlConnection(connectionString)
    Using command As New SqlCommand("dbo.PO_INSERT_WITH_LINE_ITEM", connection)
        command.CommandType = CommandType.StoredProcedure
        connection.Open()
        'add other parameters here
        'add TVP parameter
        Dim tvpParameter = command.Parameters.Add("@tvp", SqlDbType.Structured)
        tvpParameter.Value = dt
        command.ExecuteNonQuery()
        'get output parameter value here
    End Using
End Using

Insert data from ListView into table valued parameter in vb.net

1 Answers1