How do I use more than one condition in WHERE clause in UPDATE command.

Question

0.00/5 (No votes)

See more:

Hi friends,

I Have build the syntax as per suggestion with multiple WHERE criteria but it displays Following Error message:

Syntax error ( Missing Operation ) in query expression 'Sap_No = 142 AND WHERE MNT = Jan AND WHERE YR = 2018'

What is problem with Syntax and Who to Correct it ?

I Have Tried as Follows:

What I have tried:

Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
'*******************************************************************************************
BtnSave.Enabled = False : Dbconnection.Close() : Dbconnection.Open()
'LEN = LvMasterAdapter.Fill(MasterDataSet, "LeaveMaster")

Dim DesInd As Integer : Dim MN, YRR As String
LvMasterReader = LvMasterCmd.ExecuteReader()

LvMasterReader.Read()

If LvMasterReader("Mnt") = CmbMonth.Text And LvMasterReader("Yr") = CmbYear.Text And LvMasterReader("SAP_No") = TxtSapID.Text Then
MN = CmbMonth.Text : YRR = CmbYear.Text
DesInd = LvMasterReader("DesigIndex")

If LvMasterReader("LeaveStatus") = "N" Then

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= " & CInt(TxtSapID.Text) & " AND WHERE MNT= " & MN & " AND WHERE YR= " & YRR

Try
LvMasterCmd.Parameters.AddWithValue("@Sap_No", CInt(TxtSapID.Text))
LvMasterCmd.Parameters.AddWithValue("@From_Date", DtpFrom.Value)
LvMasterCmd.Parameters.AddWithValue("@To_Date", DtpTo.Value)
LvMasterCmd.Parameters.AddWithValue("@LeaveType", CmbLeaveType.Text)
LvMasterCmd.Parameters.AddWithValue("@Days", LblDays.Text)
LvMasterCmd.Parameters.AddWithValue("@MNT", CmbMonth.Text)
LvMasterCmd.Parameters.AddWithValue("@YR", CmbYear.Text)
LvMasterCmd.Parameters.AddWithValue("@LeaveStatus", "Y")

LvMasterReader.Close()

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

End Sub

Posted 27-Jul-18 0:54am

J_Mohan

Updated 27-Jul-18 1:06am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2018-07-27T01:05:00

Solution 1

Read about SQL (here the WHERE clause). The general syntax is

SQL

WHERE <search_condition>

where the search condition can be a combination using AND or OR and NOT.

So your SQL statement should contain something like

SQL

WHERE ap_No = 142 AND MNT = Jan AND YR = 2018

Related reads (here for T-SQL):
WHERE (Transact-SQL) | Microsoft Docs[^]
Search Condition (Transact-SQL) | Microsoft Docs[^]

Posted 27-Jul-18 1:05am

Jochen Arndt

Comments

J_Mohan 28-Jul-18 10:47am

Thanks Friends,

I have corrected syntax as per your suggestion :

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= " & CInt(TxtSapID.Text) & " AND MNT= " & MN & " AND YR= " & YRR

But it shows "Data Type Mismatch i criteria expression"

I have thoroughly check the type of Table field datatype and converted to Integer before ExecuteNonQuery() statement till it shows the error message.

What and where is wrong step? please Guide.

Jochen Arndt 28-Jul-18 19:07pm

I can't know the answer because I neither know the type of your database fields nor the type of your variables.

However, in your initial question the month value was passed as 'Jan' which is a string. Such must be passed quoted if stored as such strings in the database or converted to a corresponding integer otherwise.

OriginalGriff · Answer 2 · 2018-07-27T01:07:00

Try:

SQL

UPDATE LeaveMaster SET Sap_No= @Sap_No, From_Date= @From_Date, To_Date=@To_Date, LeaveType=@LeaveType, Days=@Days, LeaveStatus=@LesveType WHERE Sap_No= " & CInt(TxtSapID.Text) & " AND MNT= " & MN & " AND YR= " & YRR

But ... don't do that.
You clearly know how to use parameterised queries, so why throw a string concatenation in there and leave yourself wide open to SQL Injection?

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. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?