带有来自 vb.net 参数的 postgreSQL 查询

Question

我正在尝试编写一个选择语句(postgreSQL(，该语句使用我的应用程序屏幕中的字段。

我已经两天没有成功了。 任何帮助和/或解释等...将不胜感激。

(代码尚未完成，优化或测试，因此请原谅任何错误的编码( 在以下行收到错误：ODBCdaDups.Fill(dsDups2( 收到的错误是：错误 [42883] 错误：运算符不存在：@ 文本;&vblf & "执行查询时出错" 我尝试使用编号参数($ 1、$2 等(，但也无法弄清楚。

Private Sub Check4Duplicate()
Dim DupMessage As String
Try
Dim DupSQL As String = Nothing
Dim DupConn As New OdbcConnection()
Dim strConn As String = ""
If GlobalVariables.logProd = 1 Then
strConn = "Driver={PostgreSQL ANSI};database=SacredSelections;server=127.0.0.1;port=5432;uid=SSApp;sslmode=disable;readonly=0;protocol=7.4;User ID=SSApp;password=Cordova123;"
Else
strConn = "Driver={PostgreSQL ANSI};database=SacredSelectionsTest;server=127.0.0.1;port=5432;uid=SSApp;sslmode=disable;readonly=0;protocol=7.4;User ID=SSApp;password=Cordova123;"
End If
Dim dsDups2 As New DataSet
Dim ODBCdaDups As OdbcDataAdapter
Dim cmdbldDups As OdbcCommandBuilder
Dim cmdDups As OdbcCommand
DupConn = New OdbcConnection(strConn)
DupConn.Open()
dsDups2 = New DataSet
ODBCdaDups = New OdbcDataAdapter
cmdbldDups = New OdbcCommandBuilder(ODBCdaDups)
dsDups2.DataSetName = "auctions"
' Create the SelectCommand.
' Original SQL Server command
'cmdDups = New OdbcCommand("Select * From auctions where auction_name = @auction_name;", DupConn)
' Trying to create new postgresql command
cmdDups = New OdbcCommand("do $$
begin
select *
from auctions
where auction_name = @auction_name;
end;
$$
", DupConn)
cmdDups.Parameters.Add("auction_name", SqlDbType.Text).Value = txt_auction_name.Text
cmdDups.Prepare()
ODBCdaDups.SelectCommand = cmdDups
ODBCdaDups.Fill(dsDups2)
DupConn.Close()
If Not DupConn Is Nothing Then DupConn.Dispose()
DupMessage = ""
' Loop over tables in the DataSet.
Dim collection As DataTableCollection = dsDups2.Tables
If dsDups2.Tables(0).Rows.Count = 0 Then
DupTitle = "Unique Record - Save Allowed"
If strEditType = "Edit" Then
DupMessage = "This will save changes to an existing Unique record for Auction ID " + txt_auction_id.Text + " and Auction Name " + txt_auction_name.Text + " and Location Name " + txt_location_address.Text
Else
DupMessage = "This will create a Unique record for Auction ID " + txt_auction_id.Text + " and Auction Name " + txt_auction_name.Text + " and Location Name " + txt_location_address.Text
End If
Else
DupTitle = "Duplicate Record - Save NOT Allowed"
DupMessage = "A record already exists with Auction ID " + txt_auction_id.Text + " and Auction Name " + txt_auction_name.Text + " and Location Name " + txt_location_address.Text
MessageBox.Show(DupMessage, DupTitle)
End If
Catch ex As Exception
DupTitle = "Connection failed"
DupMessage = "Unable to Open Auction Information Connection to check for Existing Records"
MessageBox.Show(DupMessage, DupTitle)
End Try
End Sub

Answer 1

ODBC 中的参数名称无关紧要。重要的是参数在sql命令中的显示顺序必须与它们添加到ParametersCollection中的顺序匹配。

即使出现错误，Using...End Using块也会关闭并释放数据库对象。

我必须猜测字段名称和数据类型，因此，请检查数据库中的实际值并相应地调整代码。如果任何字段是数值类型，则不会将文本框的.Text属性转换为正确的类型。

通常，主键字段永远不会更新，因此请从代码中删除PK字段的更新。如果字段自动递增，则不会为该字段插入值。通过删除自动编号字段中的任何插入来调整代码。

Private Function GetConnectionString() As String
Dim strConn As String
If GlobalVariables.logProd = 1 Then
strConn = "Driver={PostgreSQL ANSI};database=SacredSelections;server=127.0.0.1;port=5432;uid=SSApp;sslmode=disable;readonly=0;protocol=7.4;User ID=SSApp;password=Cordova123;"
Else
strConn = "Driver={PostgreSQL ANSI};database=SacredSelectionsTest;server=127.0.0.1;port=5432;uid=SSApp;sslmode=disable;readonly=0;protocol=7.4;User ID=SSApp;password=Cordova123;"
End If
Return strConn
End Function
Private Sub Check4Duplicate(strEditType As String)
Dim DupMessage As String
Dim DupTitle As String
Try
Dim dt As New DataTable
Using DupConn As New OdbcConnection(GetConnectionString())
Using cmdDups As New OdbcCommand("select auction_name from auctions where auction_name = ?;", DupConn)
cmdDups.Parameters.Add("@auction_name", OdbcType.VarChar).Value = txt_auction_name.Text
dt.Load(cmdDups.ExecuteReader)
End Using
End Using
If dt.Rows.Count = 0 Then
DupTitle = "Unique Record - Save Allowed"
If strEditType = "Edit" Then
DupMessage = "This will save changes to an existing Unique record for Auction ID " + txt_auction_id.Text + " and Auction Name " + txt_auction_name.Text + " and Location Name " + txt_location_address.Text
UpdateAuction()
Else
DupMessage = "This will create a Unique record for Auction ID " + txt_auction_id.Text + " and Auction Name " + txt_auction_name.Text + " and Location Name " + txt_location_address.Text
InsertAuction()
End If
Else
DupTitle = "Duplicate Record - Save NOT Allowed"
DupMessage = $"A record already exists with Auction ID {txt_auction_id.Text} and Auction Name {txt_auction_name.Text} and Location Name {txt_location_address.Text}"
MessageBox.Show(DupMessage, DupTitle)
End If
Catch ex As Exception
DupTitle = "Connection failed"
DupMessage = "Unable to Open Auction Information Connection to check for Existing Records"
MessageBox.Show(DupMessage, DupTitle)
End Try
End Sub
Private Sub InsertAuction()
Using DupConn As New OdbcConnection(GetConnectionString())
Using cmd As New OdbcCommand("Insert Into auctions (auction_id, auction_name, auction_address) Values (?,?,?)")
cmd.Parameters.Add("id", OdbcType.VarChar).Value = txt_auction_id.Text
cmd.Parameters.Add("name", OdbcType.VarChar).Value = txt_auction_name.Text
cmd.Parameters.Add("address", OdbcType.VarChar).Value = txt_location_address.Text
DupConn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
Private Sub UpdateAuction()
Using DupConn As New OdbcConnection(GetConnectionString())
Using cmd As New OdbcCommand("Update auctions Set auction_id = ?, auction_name = ?, auction_address = ? Where aution_name = ?")
cmd.Parameters.Add("id", OdbcType.VarChar).Value = txt_auction_id.Text
cmd.Parameters.Add("name", OdbcType.VarChar).Value = txt_auction_name.Text
cmd.Parameters.Add("address", OdbcType.VarChar).Value = txt_location_address.Text
cmd.Parameters.Add("name2", OdbcType.VarChar).Value = txt_auction_name.Text
DupConn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub

这看起来像是重复构造连接和命令的代码重复，但将这些项目保留在使用它们的方法的本地，使我们能够确保它们被关闭和处置。