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@postgresql.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@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
> 


-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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