以ms访问SQL设置最大ID条件 [英] Setting max ID criteria in ms access SQL
本文介绍了以ms访问SQL设置最大ID条件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
This query does not run at the beginning. Could someone please help look at what is wrong?
This query does not run at the beginning. Could someone please help look at what is wrong?
What I have tried:
<pre> strSQL1 = "SELECT * FROM PharmSales WHERE HospitalNo='" & Me.txtRegNo & "' And TDate = #" & Format(Me.txtTDate, "M\/dd\/yyyy") & "# AND SalesItem1 = '" & Me.txtSalesItem1 & "' And PharmSalesID= (SELECT MAX(PharmSalesID) FROM PharmSales)"
Set pr = db.OpenRecordset(strSQL1)
With pr
If Not .BOF And Not .EOF Then 'Ensure that the recordset contains records
.MoveLast
.MoveFirst
If .Updatable Then 'To ensure record is not locked by another user
.Edit 'Must start an update with the edit statement
If IsNull(![TotalPaid]) = True And Me.txtGrand_TotalPay.Value >= Me.txtSalesAmt1.Value Then
![DispQty1] = Nz(![DispQty1] + Me.txtSalesQty1.Value, 0)
.Update
ElseIf IsNull(![TotalPaid]) = False And (Me.txtGrand_TotalPay.Value - Me.txtSalesAmt1.Value) >= (txtGrand_TotalFee - Me.txtGrand_TotalPay.Value + Me.txtSalesAmt1.Value) Then
![DispQty1] = Nz(![DispQty1] + Me.txtSalesQty1.Value, 0)
.Update
Else: MsgBox ("Insufficient balance!")
End If
End If
End If
pr.Close
Set pr = Nothing
Set db = Nothing
End With
End Sub
推荐答案
对于初学者,不要那样做!永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度,这可能会破坏您的整个数据库。请改用参数化查询。
其次,TextBox的默认ToString实现总是返回如下字符串:System.Windows.Forms.TextBox,Text:.. msgstr - 它不返回Text属性。
所以你的WHERE子句根本不匹配任何东西,所以不会返回任何行。
For starters, don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
Secondly, the default ToString implementation for a TextBox always returns a string like this: "System.Windows.Forms.TextBox, Text: ..." - it does not return the Text property.
So your WHERE clauses are unlikely to match anything at all, so no rows will be returned.
这篇关于以ms访问SQL设置最大ID条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文