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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: bhguo(at)163(dot)com
Subject: BUG #17500: Insert data with ODBC driver in VB.net failed
Date: 2022-05-27 11:01:57
Message-ID: 17500-36dc046dbbad9858@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2022-05-27 13:05:58 Re: BUG #17500: Insert data with ODBC driver in VB.net failed
Previous Message Michael Paquier 2022-05-27 00:00:23 Re: BUG #17485: Records missing from Primary Key index when doing REINDEX INDEX CONCURRENTLY