Click here to Skip to main content
14,932,748 members
Please Sign up or sign in to vote.
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
Updated 27-Jul-18 1:06am

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[^]
   
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.
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?
   
Comments
J_Mohan 28-Jul-18 10:56am
   
Thanks for reply friends,

But I don't want to delete table, I just want to search specific record within a table and update it with parameters values. Now I altered my Update syntax as follows:

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 I receive "Data type Mismatch in criteria expression" Error message. I thoroughly inspected Table fields and converted Parameter as Integer before ExecuteNonQuery() command.Till it shows error.

What & where the wrong step ? Please guide.
OriginalGriff 28-Jul-18 11:03am
   
I know you don't - but by concatenating strings instead of using parameterized queries, that is exactly what you are letting anyone with access to your application do: delete rows at random, delete tables, change data to suit them, bypass your password controls, steal all your data, ... anything they want to do.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900