Problem with UPDATE clause

[tonys123]

Hi Guys. I am trying to update a record in a table which needs to be found using 2 fields (i.e. WHERE xxx = qaz AND zzz=456). When I am matching on 1 field only it finds records but where I try to match on the 2 fields it fails to match. My code is as follows:

*************************************************
If (CStr(Request("MM_update")) = "theform") Then
If (Not MM_abortEdit) Then
' execute the update
Dim MM_editCmd5

Set MM_editCmd5 = Server.CreateObject ("ADODB.Command")
MM_editCmd5.ActiveConnection = MM_Project_Reporting_STRING
'MM_editCmd5.CommandText = "UPDATE dbo.ProjectReport SET Project_ID = ?, Report_Objectives = ?, Report_NextWeek = ? WHERE (Project_ID = ? AND Report_EndDate = " & WeekEndingDate & ")"
MM_editCmd5.CommandText = "UPDATE dbo.ProjectReport SET Project_ID = ?, Report_Objectives = ?, Report_NextWeek = ? WHERE (Project_ID = ?)"
MM_editCmd5.Prepared = true
MM_editCmd5.Parameters.Append MM_editCmd5.CreateParameter("param1", 5, 1, -1, MM_IIF(ProjID, ProjID, null)) ' adDouble
MM_editCmd5.Parameters.Append MM_editCmd5.CreateParameter("param2", 201, 1, 500, Request.Form("Objectives")) ' adLongVarChar
MM_editCmd5.Parameters.Append MM_editCmd5.CreateParameter("param3", 201, 1, 500, Request.Form("Activities")) ' adLongVarChar
MM_editCmd5.Parameters.Append MM_editCmd5.CreateParameter("param4", 5, 1, -1, MM_IIF(Request.Form("MM_recordId5"), Request.Form("MM_recordId5"), null)) ' adDouble
MM_editCmd5.Execute
MM_editCmd5.ActiveConnection.Close


' append the query string to the redirect URL
Dim MM_editRedirectUrl2
MM_editRedirectUrl2 = "projects.asp"
If (Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl2, "?", vbTextCompare) = 0) Then
MM_editRedirectUrl2 = MM_editRedirectUrl2 & "?" & Request.QueryString
Else
MM_editRedirectUrl2 = MM_editRedirectUrl2 & "&" & Request.QueryString
End If
End If
Response.Redirect(MM_editRedirectUrl2)
End If
End If
*************************************************
The line commented out doesn't update the db. The line below it which hasn't got the 'AND Report_EndDate' stuff in it does update the db. Immediately before we execute the 'IF (CStr….' bit of code we output the variable WeekEndingDate and it is correct. We even removed the variable and substituted it with the actual date and that still didn't work. What is going on? Any help would be ace.

 

[ethorn10]

Now, you say the WeekEndingDate is correct, but what does that mean? It might be a correct date, but are there records in the table to match that date?

I'd suggest running a quick SELECT statement using the parameters in your WHERE clause to make sure you do have that data. Or as a debugging measure, response.write the CommandText that is failing and let us know what you find.

 

[tonys123]

Hi. I guess I should have done the debug SELECT without being told. When I debugged I couldn't find the record, not because the date wasn't there but because of the format. The corrected syntax is:

MM_editCmd5.CommandText = "UPDATE dbo.ProjectReport SET Project_ID = ?, Report_Objectives = ?, Report_NextWeek = ? WHERE (Project_ID = ? AND Report_EndDate = '" & WeekEndingDate & "')"

Note the extra set of single quotes; that is what was missing.

 

[ethorn10]

Good news. Glad you figured it out.