Solved

SQL problem

Posted on 2009-07-07
11
259 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

0
Comment
Question by:DancingFighterG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 2

Assisted Solution

by:shoppedude
shoppedude earned 100 total points
ID: 24797631
Try using the REPLACE function on the necessary fields, like this:

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

Open in new window

0
 

Author Comment

by:DancingFighterG
ID: 24798790
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"

0
 
LVL 3

Expert Comment

by:QuinnDester
ID: 24798811
its the apostrophie in employees that is causing your problem, try replacing that as well
0
Technology Partners: 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!

 

Author Comment

by:DancingFighterG
ID: 24798832
Yhea, that's what I'm trying to get rid of. Trying how to get rid of the apostrophie
0
 
LVL 2

Expert Comment

by:shoppedude
ID: 24799435
Within what application is this code being used?  I'm researching the #evaluate function and not finding much on it.
0
 
LVL 3

Accepted Solution

by:
QuinnDester earned 300 total points
ID: 24799459
if its in the database try this to clean it up
update table_name
set column_name = replace(column_name, '''','')
where column_name like '%''%'
that should remove them everywhere they exist in that column

if this is out side the database you need to clean it out before you can use it in a query, clean your string in the page code first
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 24803510
replace(replace('#Evaluate("form.Form_Name" & ThisRow)#', ',', ''),chr(39),'')
0
 

Author Comment

by:DancingFighterG
ID: 24806045
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!!
0
 
LVL 32

Expert Comment

by:awking00
ID: 24806255
Sorry, Oracle uses chr(), but I think T-SQL uses char(). Change the chr(39) to char(39) and re-try.
0
 

Author Comment

by:DancingFighterG
ID: 24806347
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!!
0
 
LVL 32

Expert Comment

by:awking00
ID: 24806407
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.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pivot tables in SQL 1 45
SQL Syntax 6 51
Error creating summetric key on SQL 2012 (Amazon) 7 44
Set a variable value in SQL Procedure 3 26
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question