如何在WHERE子句中使用三个条件更新单个记录？ [英] How to UPDATE single record using three criteria in WHERE clause ?

问题描述

嗨朋友们，



我使用Visual Basic 2015作为前端和Access数据库作为后端。我想在以下三个基础上更新表中的单个记录：



WHERE



Sap_No = TxtSap_No AND



MNT（即月份）= CmbMonth.Text（即ComboBox文本）和



YR （即年份）= CmbYear.Text（即ComboBox文本）



我尝试了三种不同的UPDATE命令组合，但它提供了不同的ERROR消息：



在语法（1）和（3）中我收到 - 标准表达式中的数据类型不匹配虽然我彻底检查了所有字段类型，但它们都是正确的类型。 />


在语法（2）中我收到 - 查询表达式中字符串中的语法错误（Sap_No ='10798'）AND（MNT ='JAN'）AND（YR = '2018'）; '，



问题在哪里，解决方案是什么？



除此之外，如果我尝试省略WHERE条件，UPDATE命令用相同的值替换表中的所有记录，如果我应用WHERE Sap_No =& TxtSap_No& ，UPDATE命令替换所有具有Sap_No = TxtSap_No的记录，而不管具体的月份和年份。



解决方案是什么？我已经给出了以下所有三种语法以供参考。请帮忙。



我尝试过：



试试吧/>


'1



'LvMasterCmd.CommandText =UPDATE LeaveMaster SET Sap_No = @Sap_No，From_Date = @From_Date ，To_Date = @ To_Date，LeaveType = @ LeaveType，Days = @ Days，LeaveStatus = @ LesveType&

'WHERE Sap_No =& TxtSapID.Text& AND MNT =& MN& 和YR =& YRR& ;





'2

'LvMasterCmd.CommandText =UPDATE LeaveMaster Set [Sap_No] = ？，[From_Date] =？，[To_Date] =？，[LeaveType] =？，[Days] = ?, [LeaveStatus] =？WHERE（Sap_No ='& TxtSapID.Text&'）AND（MNT ='& MN&'）AND（YR ='& YRR&）;



'3

LvMasterCmd.CommandText =UPDATE LeaveMaster Set [Sap_No] =？，[From_Date] = ?, [To_Date] = ?, [LeaveType] = ?, [Days] = ?, [LeaveStatus] =？WHERE Sap_No =& ; CInt（TxtSapID.Text）& AND MNT =& MN& 和YR =& YRR& ;





LvMasterCmd.Parameters.AddWithValue（@ Sap_No，TxtSapID.Text）



LvMasterCmd.Parameters.AddWithValue（@ From_Date，DtpFrom.Value）

LvMasterCmd.Parameters.AddWithValue（@ To_Date，DtpTo.Value）



LvMasterCmd.Parameters.AddWithValue（@ LeaveType，LT）

LvMasterCmd.Parameters.AddWithValue（@ Days，LblDays.Text）

LvMasterCmd.Parameters.AddWithValue（@ MNT，MN）

LvMasterCmd.Parameters.AddWithValue（@ YR，YRR）

LvMasterCmd。 Parameters.AddWithValue（@ LeaveStatus，Y）



LvMasterReader.Close（）



LvMasterCmd.ExecuteNonQuery（）

LvMasterCmd.Parameters.Clear（）

退出Sub

Catch ex As Exception

MessageBox.Show（ex.Message）

结束尝试

Hi Friends,

I am using Visual Basic 2015 as Frontend and Access Database as Backend. I want to UPDATE a single record in table on the following three basis :

WHERE

Sap_No = TxtSap_No AND

MNT (i. e. Month) = CmbMonth.Text (i. e. ComboBox Text) AND

YR (i.e. Year) = CmbYear.Text (i. e. ComboBox Text)

I have tried three different combination of UPDATE command but it gives different ERROR messages :

In Syntax ( 1 ) and ( 3 ) I received - "Data type mismatch in criteria expression" though I thoroughly check all the field types , they are all of are correct type.

In Syntax ( 2 ) I received – "Syntax Error in string in query expression ‘ (Sap_No= ‘ 10798 ’) AND (MNT= ‘JAN’) AND (YR= ‘ 2018 ‘) ; ‘,

Where is the problem and what is the solution ?

Besides this if I try to omit "WHERE" condition, the UPDATE command replaces all the records in table with same values and if I apply "WHERE Sap_No = & TxtSap_No & " , the UPDATE command replaces all the records having Sap_No = TxtSap_No irrespective of specific month and year.

What will be the solution ? I have given all the three syntax below for reference. Please help.

What I have tried:

Try

'1

' LvMasterCmd.CommandText = "UPDATE LeaveMaster SET Sap_No= @Sap_No, From_Date= @From_Date, To_Date=@To_Date, LeaveType=@LeaveType, Days=@Days, LeaveStatus=@LesveType " &
'"WHERE Sap_No= " & TxtSapID.Text & " AND MNT= " & MN & " AND YR= " & YRR & " ;"


'2
'LvMasterCmd.CommandText = "UPDATE LeaveMaster Set [Sap_No]= ?, [From_Date]= ?, [To_Date]= ?, [LeaveType]= ?, [Days]= ?, [LeaveStatus]= ? WHERE (Sap_No= ' " & TxtSapID.Text & " ' ) AND (MNT= ' " & MN & " ') AND (YR= ' " & YRR & " ) ;"

'3
LvMasterCmd.CommandText = "UPDATE LeaveMaster Set [Sap_No]= ?, [From_Date]= ?, [To_Date]= ?, [LeaveType]= ?, [Days]= ?, [LeaveStatus]= ? WHERE Sap_No= " & CInt(TxtSapID.Text) & " AND MNT= " & MN & " AND YR= " & YRR & " ;"


LvMasterCmd.Parameters.AddWithValue("@Sap_No", TxtSapID.Text)

LvMasterCmd.Parameters.AddWithValue("@From_Date", DtpFrom.Value)
LvMasterCmd.Parameters.AddWithValue("@To_Date", DtpTo.Value)

LvMasterCmd.Parameters.AddWithValue("@LeaveType", LT)
LvMasterCmd.Parameters.AddWithValue("@Days", LblDays.Text)
LvMasterCmd.Parameters.AddWithValue("@MNT", MN)
LvMasterCmd.Parameters.AddWithValue("@YR", YRR)
LvMasterCmd.Parameters.AddWithValue("@LeaveStatus", "Y")

LvMasterReader.Close()

LvMasterCmd.ExecuteNonQuery()
LvMasterCmd.Parameters.Clear()
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

推荐答案

从不连接字符串开始形成SQL命令。你知道参数化查询，为什么你突然同时使用它们？



你已经将Sap_no作为参数传递，只需使用该值而不是尝试发送两次。 ..



和BTW：如果您确切知道Sap_No已经具有什么价值，那么将Sap_No设置为UPDATE的一部分是什么意思？

Start by never concatenation strings to form an SQL command. You know about parameterised queries, so why are you suddenly using both?

You already pass Sap_no as a parameter, just use that value instead of trying to send it twice ...

And BTW: what is the point of setting Sap_No as part of the UPDATE if you know exactly what value it already has?

这篇关于如何在WHERE子句中使用三个条件更新单个记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！