asked on

Update of record only occurs if textfield contains text

I have a repeating region that displays records. A person reviewing the records has an option to either ACCEPT or REJECT an order. They also have an option to enter a comment in the cmNotes field.

The REJECT portion is working correctly but I am having issues with the ACCEPT portion. The below code works if records are ACCEPTED and information is entered into the cmNotes field. But if an order is ACCEPTED, but no comments are entered into the cmNotes textfield, I get an error. The error is below.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ID ='.

I want this code to allow a person to ACCEPT without the need to enter text in the cmNotes field. If the field is either blank or contains text, the record is updated.

If mrec<>"" Then

	
	Set MM_editCmd = Server.CreateObject("ADODB.Command")
        MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tampaorders100.mdb"

	
	arrMrec=split(mrec,",")	
	for i = 0 to ubound(arrMrec)
		
	        mquery= "update tampaorders set processed='Yes', Status='Accepted', cmNotes='" & _
				Request.Form("cmNotes" & arrMrec(i)) & "', Who='"&Request.Form("Who")& _
				"', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";" 
		
    	        MM_editCmd.CommandText = mquery
		
		MM_editCmd.Execute
	next
	
    
	MM_editCmd.ActiveConnection.Close

End If
	

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If

Open in new window

Daniel Wilson

There may be a simpler way ... but I would write an IF statement that would determine whether to include cmNotes in the UPDATE.

mquery= "update tampaorders set processed='Yes', Status='Accepted', "
if len(trim(Request.Form("cmNotes" & arrMrec(i)))) > 0 then
    mquery = mquery & " cmNotes='" Request.Form("cmNotes" & arrMrec(i)) & "', "
end if
mquery = mquery & " Who='"&Request.Form("Who")& _
				"', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";"

Open in new window

GRayL

Replace:

cmNotes='" & Request.Form("cmNotes" & arrMrec(i)) & "'

with:

cmNotes='" & Nz(Request.Form("cmNotes" & arrMrec(i)),"") & "'

arendt73

ASKER

In attempting to use DanielWilson's suggestion, I get the following error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

mquery = mquery & " cmNotes='" Request.Form("cmNotes" & arrMrec(i)) & "', "
----------------------------------------------------^

arendt73

ASKER

When using GRayL's suggestion, I get the following error:

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'Nz'

Andrew_Webster

NZ is a VBA function that replaces a Null with a specified value. i.e. Nz(myVariable, "") would replace a Null value for myVariable with an empty string.

Which brings me to a workaround: concatenate an empty string to cmNotes so that there is a value to enter come what may.

e.g.:

mquery= "update tampaorders set processed='Yes', Status='Accepted', cmNotes='" & _
                                Request.Form("cmNotes" & arrMrec(i)) & "' & "", Who='"&Request.Form("Who")& _
                                "', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";"

Open in new window

Note the & "".

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arendt73

ASKER

Attempted Andrew Wbster's suggestion and received the follwoing error message after attempting the update of several records:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression ''' & ", Who='RFA', ViewDate=now(), ViewDate1=date() where ID = 369365;'.

/orders/cm_review.asp, line 106

Line 106 in the code is between

MM_editCmd.CommandText = mquery

MM_editCmd.Execute

GRayL

I suggest you develop the query in the Query Designer and when you get what you want, save it as a named query and execute the query from code.

GRayL

That is a heck of a lot easier than trying to do what you are doing. Regardless, you should always develop a query in the Designer, paste the SQL in your code, add the quotes and line continuations and go from there. With that approach, you always have a baseline which you know works.

arendt73

ASKER

Thanks for the suggestion GRayL. I wish I knew how to use the Query Designer in Access. Everytime I attempt to use it, it never works out. If you have any directions or a site that has specific directions on using the query designer, I'd greatly appreciate the information.

Thank you.

GRayL

Then what do you use to design a query in Access?