Re: BUG #17500: Insert data with ODBC driver in VB.net failed

You should contact the ODBC maintainers:

https://odbc.postgresql.org/faq.html#1.6

pgsql-odbc(at)postgresql(dot)org

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

On Fri, May 27, 2022 at 11:01:57AM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17500
> Logged by: bob guo
> Email address: bhguo(at)163(dot)com
> PostgreSQL version: 10.0
> Operating system: win7
> Description:
>
> Dear Sirs,
>
> I need to write data to Postgresql with ODBC driver in VB.net, all work fine
> until today's problem.
>
> I have simplified the scene with a simple table, it has only two fields: id
> and birthday, and I write only two rows of simple data, they are:
> 2 null
> 1 2022-1-2
>
> When I load the data into a datatable dt and use adapter.Update(dt) to write
> Postgresql, the program will raise an exception, it says in chinese:
> 无法将类型为“System.DateTime”的对象强制转换为类型“System.Char[]”
>
> On the contrary, If I exchange the order of rows like this:
> 1 2022-1-2
> 2 null
> The program will work well.
>
> I can't image what's wrong with it. I have tried it with other database,
> such as SQL Server, Access, MySQL, they all work fine. So I guess there is
> some bug in Postgresql ODBC driver. Please check it, thanks a lot.
>
> Attach files:
>
> The create script of table:
> CREATE TABLE schema1.table1
> (
> id bigint,
> birthday timestamp without time zone
> )
> WITH (
> OIDS = FALSE
> )
> TABLESPACE pg_default;
>
> The simplified vb.net code:
> Private Sub Button5_Click(sender As Object, e As EventArgs) Handles
> Button5.Click
> Try
>
> Dim conn As New OdbcConnection("Driver={PostgreSQL
> Unicode};Port=5432;Server=127.0.0.1;Database=test;Uid=postgres;Pwd=123456;")
> conn.Open()
>
> '1. Build a datatable with 2 rows of data
> Dim dt As New DataTable
> dt.Columns.Add("id", GetType(String))
> dt.Columns.Add("birthday", GetType(DateTime))
> dt.Rows.Add(New Object() {2})
> dt.Rows.Add(New Object() {1, New DateTime(2022, 1, 2)})
>
> '2. Build a OdbcCommand
> Dim com As New OdbcCommand("insert into schema1.table1
> (id,birthday) values(?,?)", conn)
> Dim p1 As New OdbcParameter, p2 As New OdbcParameter
> p1.SourceVersion = DataRowVersion.Current
> p1.SourceColumn = "id"
> p2.SourceVersion = DataRowVersion.Current
> p2.SourceColumn = "birthday"
> com.Parameters.Add(p1)
> com.Parameters.Add(p2)
>
> '3. Perform insert action
> Using adapter As New OdbcDataAdapter()
> adapter.InsertCommand = com
> adapter.Update(dt)
> End Using
>
> '4. Close
> dt.Clear()
> conn.Close()
>
> Catch ex As Exception
> 'An exception will raise, in chinese word it says:
> 无法将类型为“System.DateTime”的对象强制转换为类型“System.Char[]”
> MsgBox(ex.Message)
> Finally
> MsgBox("Finished!")
> End Try
>
> End Sub
>

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson