sql - 带有来自 vb.net 的参数的 postgreSQL 查询
问题描述
我正在尝试编写一个使用我的应用程序屏幕中的字段的选择语句(postgreSQL)。
我已经2天没有成功。任何帮助和/或解释等......将不胜感激。
下面是整个子。(代码尚未完成、优化或测试,因此请原谅任何错误的编码)在以下行收到错误: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
解决方案
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
看起来像是重复构建连接和命令的代码重复,但将这些项目保持在使用它们的方法的本地允许我们确保它们已关闭和处置。
推荐阅读
- html - 嵌套 id 的 CSS 不会被覆盖
- c# - 从 Xml 响应中获取特定元素值 (RestSharp/C#)
- javascript - 为什么我的两种方法在调用 onClick 时都不起作用?
- c++ - 将鼠标光标移动到 Raspbian、OpenCV 中的某个位置
- distance - 为什么测量到一定距离后我的 LED 不亮?Atmega328p 和超声波 HC-SR04 传感器
- wso2 - WSO2 身份服务器 - 根据用户角色限制服务提供商的入站身份验证
- react-native - React Native如何计算文本的字符长度?
- java - 将 int 转换为双 java
- amazon-web-services - Lambda@edge 记录发布请求 ID 但不发布控制台日志
- react-native - @react-native-community/picker 抛出注册两个同名视图的错误