From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bhguo@163.com
Subject: BUG #17500: Insert data with ODBC driver in VB.net failed
Date: Fri, 27 May 2022 11:01:57 +0000
Message-ID: <17500-36dc046dbbad9858@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17500
Logged by: bob guo
Email address: bhguo@163.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, bhguo@163.com
Subject: Re: BUG #17500: Insert data with ODBC driver in VB.net failed
In-Reply-To: <17500-36dc046dbbad9858@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox