• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

SQL Search with quotation marks

I have a sp I call from a VS C# program. All works fine until a get to a vendor who has a quotation mark in their ID. There is a vendor in the database whose vendorid is TOURNEDO'S. How do a change this script to find that vendor?

DECLARE @VENDORID CHAR(15)
SET @VENDORID = 'TOURNEDO''S'
SELECT PM.VADDCDPR,PM.VADCDTRO,PM.PYMTRMID,PA.TAXSCHID,PA.SHIPMTHD,
PM.TEN99TYPE,PM.TEN99BOXNUMBER,COALESCE(PT.DUEDTDS,0),COALESCE(PT.DISCDTDS,0),
COALESCE(PT.DSCPCTAM,0)
FROM PM00200 PM
INNER JOIN PM00300 PA ON PM.VENDORID=PA.VENDORID AND PM.VADCDTRO=PA.ADRSCODE
LEFT OUTER JOIN SY03300 PT ON PM.PYMTRMID = PT.PYMTRMID
WHERE PM.VENDORID = @VENDORID
0
rwheeler23
Asked:
rwheeler23
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What error message is being generated?  Your code as written should work.

If you are trying to pass the string TOURNEDO'S as a SP parameter, you may have to convert the ' to '' in your C# front-end, then pass it as a parameter.
0
 
rwheeler23Author Commented:
I ran SQL profiler to see what was sent down. This is what was sent down.

exec rbsPMTransaction @INTERID=N'PEF',@BCHSOURC=N'PM_Trxent',@BACHNUMB=N'BOA-2012-09-001',@VCHNUMWK=N'0246254          ',@CNTRLTYP=0,@DOCNUMBR=N'TXN00022902',@DOCTYPE=4,@TRXDSCRN=N'',@DOCDATE='2012-05-25 00:00:00:000',@PSTGDATE='2012-05-25 00:00:00:000',@PORDNUMBR=N'',@VENDORID=N'TOURNEDO''S     ',@DOCAMNT=1082.0400

My SP insert records into a table which does not allow nulls. I then copied this into SQL and it returns no records. This is the Select statement is used to gather data from the vendor master record. It returns no records because variable @VENDORID contains 'TOURNEDO''S'. For other vendors it works fine. From what I can tell the right value is being passed down. It is simply the SQL statement that is failing.

SELECT @VADDCDPR=PM.VADDCDPR,@VADCDTRO=PM.VADCDTRO,@PYMTRMID=PM.PYMTRMID,@TAXSCHID=PA.TAXSCHID,@SHIPMTHD=PA.SHIPMTHD,
@TEN99TYPE=PM.TEN99TYPE,@TEN99BOXNUMBER=PM.TEN99BOXNUMBER,@DUEDTDS=COALESCE(PT.DUEDTDS,0),@DISCDTDS=COALESCE(PT.DISCDTDS,0),
@DSCPCTAM=COALESCE(PT.DSCPCTAM,0)
FROM PM00200 PM
INNER JOIN PM00300 PA ON PM.VENDORID=PA.VENDORID AND PM.VADCDTRO=PA.ADRSCODE
LEFT OUTER JOIN SY03300 PT ON PM.PYMTRMID = PT.PYMTRMID
WHERE PM.VENDORID = @VENDORID
0
 
keyuCommented:
its nvarchar  or varchar ?

as you mentioned vchare and its taking as parameter nvarchar

also there is some space while passing it as parameter...

revove it using rtrim option


DEclare @tablsql as nvarchar(max)

set @tablsql='TOURNEDO''S     '

print @tablsql+'[[[['

print rtrim(@tablsql)+']]]]'
0
 
rwheeler23Author Commented:
Thanks for your help with this. It turns out I was lied to on this one. I was told there would always be at least one record in the PM00300 table for every record in the PM00200 table. Well I just happened to land on one where that was not the case. So no records were being returned.

I has since upgraded my script to a left outer join and fill in missing valuies with blanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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