Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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

0
arendt73
Asked:
arendt73
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Daniel WilsonCommented:
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

0
 
GRayLCommented:
Replace:

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

with:

cmNotes='" & Nz(Request.Form("cmNotes" & arrMrec(i)),"") & "'
0
 
arendt73Author Commented:
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)) & "', "
----------------------------------------------------^
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Microsoft VBScript runtime  error '800a000d'

Type mismatch: 'Nz'
0
 
Andrew_WebsterCommented:
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 & "".
0
 
Daniel WilsonCommented:
I think this code is ASP (VBScript) code, not Access (VBA) code, right?  If so, I think Nz is mistaken.

My mistake was the omission of an ampersand (&)
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

0
 
arendt73Author Commented:
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
0
 
GRayLCommented:
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.  
0
 
GRayLCommented:
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.  
0
 
arendt73Author Commented:
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.
0
 
GRayLCommented:
Then what do you use to design a query in Access?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now