We help IT Professionals succeed at work.

SQL problem

DancingFighterG
on
Medium Priority
295 Views
Last Modified: 2012-05-07
Hello, I have this sql that updates a flag in the database but I am having a problem with file names that have commas in them. It's causing a problem in running the sql. How can I modify this code so that it looks at the entire file and negates commas.
UPDATE HRForms
	SET Form_Name = '#Evaluate("form.Form_Name" & ThisRow)#',
		Category_ID = '#Evaluate("form.Category_ID" & ThisRow)#',
		Form_File = '#Evaluate("form.Form_File" & ThisRow)#',
		Status = '#Evaluate("form.Status" & ThisRow)#'
		WHERE HRForm_ID = #Evaluate("form.HRForm_ID" & ThisRow)#

Open in new window

Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Still giviing me the same error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 's'.


SQL = "UPDATE HRForms SET Form_Name = replace('Employee's', ',', ''), Category_ID = '18', Form_File = replace('Employee's.xls', ',', ''), Status = '1' WHERE HRForm_ID = 277"

its the apostrophie in employees that is causing your problem, try replacing that as well

Author

Commented:
Yhea, that's what I'm trying to get rid of. Trying how to get rid of the apostrophie
Within what application is this code being used?  I'm researching the #evaluate function and not finding much on it.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
awking00Information Technology Specialist
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This is what I have now:

UPDATE HRForms
      SET Form_Name = replace(replace('#Evaluate("form.Form_Name" & ThisRow)#', ',', ''),chr(39),''),
            Category_ID = '#Evaluate("form.Category_ID" & ThisRow)#',
            Form_File = replace(replace('#Evaluate("form.Form_Name" & ThisRow)#', ',', ''),chr(39),''),
            Status = '#Evaluate("form.Status" & ThisRow)#'
            WHERE HRForm_ID = #Evaluate("form.HRForm_ID" & ThisRow)#

Caused an error. Will try the update clean now!!
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Sorry, Oracle uses chr(), but I think T-SQL uses char(). Change the chr(39) to char(39) and re-try.

Author

Commented:
Ok, this is what I did:

UPDATE HRForms
    SET Form_Name = REPLACE(Form_Name, '''', '')
    WHERE (Form_Name LIKE '%''%')
    UPDATE    HRForms
    SET Form_File = REPLACE(Form_File, '''', '')
    WHERE     (Form_File LIKE '%''%')
      update HRForms
      set Form_File = replace(Form_File, '''','')
            where Form_File like '%''%'

Worked just fine so that when they upload a file it takes the ' out after the insert!! Thanks guys!!
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
I think you could have also used the char() function like replace(form_name,char(39),''), but '''' is just as good. It's just that I never remember whether or not to use three or four single quotes to do the escape. :-) Glad you got it figured out.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.