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

Question

1.00/5 (2 votes)

See more:

VS2015

Hi friends,

I am trying to update single record in access table but following error encounters

Conversion from string "UPDATE LeaveMaster SET From_Date" to type long string is not valid

Please help

What I have tried:

LvMasterCmd.CommandText = "UPDATE LeaveMaster SET From_Date= @From_Date, To_Date=@To_Date, LeaveType=@LeaveType, Days=@Days, LeaveStatus=@LesveType WHERE Sap_No= '" & TxtSapID.Text And "Where MNT= '" & CmbMonth.Text And "Where YR= '" & CmbYear.Text

'Sap_No = @Sap_No,

Try
' LvMasterCmd.Parameters.AddWithValue("@Sap_No", CInt(TxtSapID.Text))
LvMasterCmd.Parameters.AddWithValue("@From_Date", DtpFrom.Value.ToString)
LvMasterCmd.Parameters.AddWithValue("@To_Date", DtpTo.Value.ToString)
LvMasterCmd.Parameters.AddWithValue("@LeaveType", CmbLeaveType.Text)
LvMasterCmd.Parameters.AddWithValue("@Days", LblDays.Text)
LvMasterCmd.Parameters.AddWithValue("@LeaveType", "Y")

LvMasterReader.Close()

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

Posted 23-Jul-18 7:04am

J_Mohan

Updated 23-Jul-18 7:17am

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)

Wendelius · Answer 1 · 2018-07-23T07:10:00

Solution 1

You have already used parameters in the SET clause. To correctly define the WHERE clause you should also use parameters in it.

At the moment you have two WHERE clauses. Instead of that you shoudl have only one. In other words:

VB

LvMasterCmd.CommandText = "UPDATE LeaveMaster SET From_Date= @From_Date, To_Date=@To_Date, LeaveType=@LeaveType, Days=@Days, LeaveStatus=@LesveType WHERE Sap_No= @sapno And MNT= @month And YR=@year"

Posted 23-Jul-18 7:10am

Wendelius

Comments

J_Mohan 23-Jul-18 14:59pm

Thanks friend, I got it and I will try it.

J_Mohan 23-Jul-18 15:09pm

I have Updated syntax as per your instructions:

LvMasterCmd.CommandText = "UPDATE LeaveMaster SET From_Date= @From_Date, To_Date=@To_Date, LeaveType=@LeaveType, Days=@Days, LeaveStatus=@LeaveStatus WHERE Sap_No= @Sap_No AND MNT = @Mnt And YR = @yr"

It Shows "Data type mismatch in criteria expression"

Wendelius 23-Jul-18 16:57pm

Data type mismatch happens if the type of the column is not the same as the type of the parameter. Re-check all the parameters and their types compared to the corresponding column types. For example, could it be that the columns are numeric in the database? If that would be the case, define the parameters numeric as well.

MadMyche · Answer 2 · 2018-07-23T07:18:00

The basic error in your SQL is that the additional conditions are just "and" and not "And where"

The scratch-my-head problem is why the first half of your query uses parameters and the second does not. THIS NEEDS TO BE FIXED!

Part 1 will be to fix your WHERE clauses:

VB

LvMasterCmd.CommandText = "UPDATE LeaveMaster SET From_Date= @From_Date, To_Date=@To_Date, LeaveType=@LeaveType, Days=@Days, LeaveStatus=@LesveType WHERE Sap_No= @SapNo AND MNT = @Mnt And YR = @yr"

And Part 2 will be to add the new parameters:

VB

LvMasterCmd.Parameters.AddWithValue("@From_Date", DtpFrom.Value.ToString)
LvMasterCmd.Parameters.AddWithValue("@To_Date", DtpTo.Value.ToString)
LvMasterCmd.Parameters.AddWithValue("@LeaveType", CmbLeaveType.Text)
LvMasterCmd.Parameters.AddWithValue("@Days", LblDays.Text)
LvMasterCmd.Parameters.AddWithValue("@LeaveType", "Y")

' added params
LvMasterCmd.Parameters.AddWithValue("@Sap_No", TxtSapID.Text)
LvMasterCmd.Parameters.AddWithValue("@MNT", CmbMonth.Text )
LvMasterCmd.Parameters.AddWithValue("@YR", CmbYear.Text)