时间戳

首页 > 解决方案 > PostgreSQL：VB.NET ODBC 更新无法正常工作

vb.net - PostgreSQL：VB.NET ODBC 更新无法正常工作

问题描述

VB.NET 2019

Postgre 10

Postgre ODBC

DBeaver 中的源数据：

https://i86.fastpic.ru/big/2019/0716/b9/_8741664f3e9495646d995a9d96b846b9.png

试图改变 SQL 状态，改变参数传递：

'Dim command As New Odbc.OdbcCommand("UPDATE public.v8users SET Data = @data WHERE ID = @id", Connection) 'MSSQL syntax
'Dim command As New Odbc.OdbcCommand("UPDATE public.v8users SET data = ?, name = ? WHERE id = ?", Connection)
'Dim command As New Odbc.OdbcCommand("UPDATE public.v8users SET data = ?data WHERE id = ?id", Connection) 'MySQL syntax
'Dim command As New Odbc.OdbcCommand("UPDATE public.v8users SET data = ? WHERE id = decode(encode(?, 'hex'), 'hex')", Connection) 'PostgreSQL syntax

'command.Parameters.Add(New Odbc.OdbcParameter("@id", SQLUser.ID))
'command.Parameters.Add(New Odbc.OdbcParameter("@data", NewBytes))
'command.Parameters.Add(New Odbc.OdbcParameter("id", SqlDbType.Binary)).Value = SQLUser.ID
'command.Parameters.Add(New Odbc.OdbcParameter("data", SqlDbType.Binary)).Value = NewBytes
'command.Parameters.Add(New Odbc.OdbcParameter("name", Odbc.OdbcType.Binary)).Value = NewBytes

Structure SQLUser
   Dim ID As Byte()
   Dim IDStr As String
   Dim Name As String
   Dim Descr As String
   Dim Data As Byte()
   Dim DataStr As String
   Dim PassHash As String
   Dim PassHash2 As String
   Dim AdmRole As String
   Dim KeySize As Integer
   Dim KeyData As Byte()
End Structure

Dim a
Dim Connection = New Odbc.OdbcConnection("Driver={PostgreSQL Unicode};Server=192.168.2.10;Database=TestPurp;Uid=<correct>;Pwd=<correct>;UseServerSidePrepare=1;ReadOnly=0")
Dim command As New Odbc.OdbcCommand("UPDATE public.v8users SET data = ? WHERE id = ?", Connection)
command.Parameters.Clear()
command.Parameters.Add(New Odbc.OdbcParameter("id", Odbc.OdbcType.Binary)).Value = SQLUser.ID
command.Parameters.Add(New Odbc.OdbcParameter("data", Odbc.OdbcType.Binary)).Value = NewBytes
a = command.ExecuteNonQuery()

a = 0，期望 a = 1

标签： vb.netpostgresqlodbc

解决方案

尝试按照它们出现在 Update 语句中的相同顺序添加参数。首先添加数据，然后添加 id。

command.Parameters.Add(New Odbc.OdbcParameter("data", Odbc.OdbcType.Binary)).Value = NewBytes
command.Parameters.Add(New Odbc.OdbcParameter("id", Odbc.OdbcType.Binary)).Value = SQLUser.ID

这与使用 OleDb 提供程序的 Access 中的工作方式相同。

推荐阅读