how to send a table for odbc in vb

Question

I'm trying to send a table for odbc in vb. the datatable parameter is P_DETALLE, but I get an error like this:

There is no assignment of object type System.Data.DataTable to a native type of a known managed provider

the code is something like this:

Sub ADMINISTRAR_ARTEFACTO(ByVal P As E_ARTEFACTO)
    Dim CONECTION As New OdbcConnection
    Try
        CONECTION.ConnectionString = ConfigurationManager.ConnectionStrings("ALURHE_DBConnectionString_64").ConnectionString + ";pwd=123"
        Dim COMMAND As OdbcCommand = New OdbcCommand("{call SP_ADM_ARTEFACTO (?,?,?,?,?,?,?,?)}", CONECTION)

        With COMMAND

            .Parameters.AddWithValue("V_SERIE", P.P_SERIE)
            .Parameters.AddWithValue("V_TIPO_ARTEFACTO", P.P_TIPO_ARTEFACTO)
            .Parameters.AddWithValue("V_MODELO", P.P_MODELO)
            .Parameters.AddWithValue("V_MARCA", P.P_MARCA)

            .Parameters.AddWithValue("V_DETALLE", P.P_DETALLE)
            .Parameters.AddWithValue("V_USUARIO", P.P_USUARIO)
            .Parameters.AddWithValue("V_ID_ARTEFACTO", If(P.P_TIPO_OPERACION = "M", P.P_ID_ARTEFACTO, DBNull.Value))
            .Parameters.AddWithValue("V_TIPO_OPERACION", P.P_TIPO_OPERACION)


        End With


        'If P.P_TIPO_OPERACION = "M" Then
        '    COMMAND.Parameters.AddWithValue("@ID_PRODUCTO", P.P_ID_PRODUCTO)
        'End If

        CONECTION.Open()
        COMMAND.ExecuteNonQuery()
        CONECTION.Close()

    Catch ex As Exception
        If (CONECTION.State = ConnectionState.Open) Then
            CONECTION.Close()
        End If

        Throw ex
    End Try
End Sub

score 0 · Accepted Answer · answered Mar 04 '18 at 21:08

0

A DataTable cannot be the value of a command parameter with the ODBC provider, it is not supported.

You could do it with the MSSQL provider if you specified SqlDbType.Structured.

One thing you could do is insert the data in from your DataTable into a temp table and then do a join on that table in your query/procedure.

answered Mar 04 '18 at 21:08

Crowcoder

9,566
3
30
39

If your data table isn't too large just loop over the rows and insert each one into a temp or stage table. I don't know your data so I can't write the query with the JOIN but it would be very similar to what your procedure already has. – Crowcoder Mar 05 '18 at 11:56
You can give me a simple example please – nasck Mar 05 '18 at 16:52
or can you tell me that other method than ODBC ?, I'm using postgresql with vb – nasck Mar 05 '18 at 17:14
[Maybe this will help](https://stackoverflow.com/questions/30320418/insert-the-whole-value-of-datatable-bulk-into-postgresql-table), it is difficult to get specific because I have no idea what the structure of your DataTable is or what is in your procedure. – Crowcoder Mar 05 '18 at 18:04
Npgsql supports datatable? – nasck Mar 05 '18 at 21:30
I don't think it supports it as a parameter, the example is using `NpgsqlDataAdapter` to perform updates based on the state of the DataRows if you are familiar with using adapters. – Crowcoder Mar 05 '18 at 21:34
Is there a way to send a datatable parameter? – nasck Mar 05 '18 at 22:24
Not that I am aware of but I don't use postgres – Crowcoder Mar 05 '18 at 23:45
but I mean in vb – nasck Mar 06 '18 at 14:45
The .Net provider must support it as the MSSQL provider supports SqlDbType.Structured. I don't think the postgress provider for .Net supports it. You will need to find another way, it seems to me you could accomplish the same thing in your stored procedure by accessing a table that has the data from your DataTable. – Crowcoder Mar 06 '18 at 14:52

how to send a table for odbc in vb

1 Answers1