[MS SQL] - stored procedure single quote handling

[DJ Chicken]

Ik heb de volgende stored procedure:

SQL:

CREATE PROCEDURE SearchCompanyBp
@name           varchar(50)
AS

select      contact.contact_id,
    contact.[name],
    isnull(address.city, '') as city,
    isnull(person.mrmrs, '') as mrmrs,
    isnull(person.initials, '') as initials,
    isnull(person.title, '' ) as title,
    isnull(person.firstname, '') as firstname,
    isnull(person.lastname, '') as lastname,
    isnull(phone.phone, '') as phonenr,
    isnull(phone1.phone, '') as ext,
    isnull(phone2.phone, '') as mobilenr,
    isnull(email.email_address, '') as emailaddress
from crm5.contact contact
    left join crm5.address address on contact.contact_id = address.owner_id and address.atype_idx = 2
    left join crm5.person person on contact.contact_id = person.contact_id
    left join crm5.phone phone on contact.contact_id = phone.owner_id and phone.ptype_idx = 1
    left join crm5.phone phone1 on person.person_id = phone1.owner_id and phone1.ptype_idx = 16385
    left join crm5.phone phone2 on person.person_id = phone2.owner_id and phone2.ptype_idx = 16389
    left join crm5.email email on person.person_id = email.person_id and email.rank = 1
where contact.[name] = @name
order by contact.[name], firstname, lastname
GO

De parameter/variabele @name wordt extern geleverd, hoe maakt ff niet uit want dat werkt.
Alleen als die @name een naam met een single quote bevat loopt de stored procedure niet. Dit is logisch want met quotes kan je strings aangeven in ms sql. Hoe geef ik IN deze stored procedure aan dat bij @name bepaalde caracters verwijderd worden voordat deze stored procedure draait.
Voor zover ik weet moet het ergens tussen 'AS' en 'SELECT' komen te staan, maar meer nuttig kon ik helaas niet achterhalen.

[ Voor 7% gewijzigd door DJ Chicken op 16-05-2006 13:57 ]

[TheRookie]

kan je niet eenvoudig replace gebruiken in de where, of is dat "te laat":

SQL:

 WHERE contact.[name] = REPLACE(@name,'\'','')

edit:

Is het geen optie om de 'externe' aanlevering ervoor te laten zorgen dat de ' ge-escaped is ?

[ Voor 60% gewijzigd door TheRookie op 16-05-2006 14:20 ]

[whoami]

Hoe roep je die stored procedure aan in je applicatie ?

theRookie: quotes escape je in Sql Server niet mbhv backslashes, maar met dubbele quotes.

[ Voor 47% gewijzigd door whoami op 16-05-2006 15:08 ]

[DJ Chicken]

whoami schreef op dinsdag 16 mei 2006 @ 14:33:
Hoe roep je die stored procedure aan in je applicatie ?

C#:

SqlCommand cmd = new SqlCommand("SearchCompanyBp", con);  
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 2000));
cmd.Parameters["@name"].Value = Request["name"].ToString();

SqlDataAdapter SqlAdap = new SqlDataAdapter(cmd);
DataTable DTable = new DataTable();
SqlAdap.Fill(DTable);

[ Voor 32% gewijzigd door DJ Chicken op 16-05-2006 14:43 ]

[_js_]

Wat werkt precies niet? Krijg je geen resultaten terug?

Kan dat komen doordat toen je de data in de database stopte iets te veel hebt geëscaped?

Want het hele idee van parameters is juist dat het niet meer uit maakt of er quotes of wat dan ook in de waarde zitten.

[DJ Chicken]

_js_ schreef op dinsdag 16 mei 2006 @ 14:43:
Wat werkt precies niet? Krijg je geen resultaten terug?

Kan dat komen doordat toen je de data in de database stopte iets te veel hebt geëscaped?

Want het hele idee van parameters is juist dat het niet meer uit maakt of er quotes of wat dan ook in de waarde zitten.

Sjah dat de parameters het quotes probleem op moesten lossen dacht ik dus ook, dat las ik ook overal. Dus ik vind het zelf ook vreemd met die quote, maar ik weet 100% zeker dat het door de quote komt.

De database heb ik niet zelf gemaakt, ik maak alleen een applicatie om er gegevens uit te halen wat lukt, totdat de parameter dus een quote bevat

[whoami]

Idd, als je gebruik maakt van parameters, zou het niet mogen uitmaken of er nu quotes of niet in je parameter zitten.
Als je geen resultaten terugkrijgt; ben je dan wel zeker dat er een record is voor de verkregen parameter ?

[TheRookie]

Krijg je dan geen resultaten terug of klapt de Stored procedure met een error ?

Als je de SP handmatig uitvoert en de @name door de waarde vervangt werkt 't dan wel ?

[DJ Chicken]

TheRookie schreef op dinsdag 16 mei 2006 @ 14:47:
Krijg je dan geen resultaten terug of klapt de Stored procedure met een error ?

Als je de SP handmatig uitvoert en de @name door de waarde vervangt werkt 't dan wel ?

Als ik hem zo uitvoer krijg ik de volgende foutmelding:

---

Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 's'.

---

SQL:

select      contact.contact_id,
    contact.[name],
    isnull(address.city, '') as city,
    isnull(person.mrmrs, '') as mrmrs,
    isnull(person.initials, '') as initials,
    isnull(person.title, '' ) as title,
    isnull(person.firstname, '') as firstname,
    isnull(person.lastname, '') as lastname,
    isnull(phone.phone, '') as phonenr,
    isnull(phone1.phone, '') as ext,
    isnull(phone2.phone, '') as mobilenr,
    isnull(email.email_address, '') as emailaddress
from crm5.contact contact
    left join crm5.address address on contact.contact_id = address.owner_id and address.atype_idx = 2
    left join crm5.person person on contact.contact_id = person.contact_id
    left join crm5.phone phone on contact.contact_id = phone.owner_id and phone.ptype_idx = 1
    left join crm5.phone phone1 on person.person_id = phone1.owner_id and phone1.ptype_idx = 16385
    left join crm5.phone phone2 on person.person_id = phone2.owner_id and phone2.ptype_idx = 16389
    left join crm5.email email on person.person_id = email.person_id and email.rank = 1
where contact.[name] = ''s Heeren Loo Zorggroep''
order by contact.[name], firstname, lastname

Inderdaad dus die 's in de zoekstring.

[ Voor 29% gewijzigd door DJ Chicken op 16-05-2006 15:02 ]

[whoami]

Ja, maar nu maak je geen gebruik van een parameter; je hebt de waarde van de parameter nu zo in de SP geplakt.

Als je de SP via je applicatie uitvoert, kijk dan eens mbhv Sql Profiler wat er daadwerkelijk naar Sql Server gestuurd wordt.

[DJ Chicken]

TheRookie schreef op dinsdag 16 mei 2006 @ 14:17:
kan je niet eenvoudig replace gebruiken in de where, of is dat "te laat":

SQL:

 WHERE contact.[name] = REPLACE(@name,'\'','')

edit:

Is het geen optie om de 'externe' aanlevering ervoor te laten zorgen dat de ' ge-escaped is ?

Hoe voeg ik dit in, want als ik dit gebruik wordt alles erna rood, dus string.

SQL:

CREATE PROCEDURE SearchCompanyBptest

AS

select      contact.contact_id,
    contact.[name],
    isnull(address.city, '') as city,
    isnull(person.mrmrs, '') as mrmrs,
    isnull(person.initials, '') as initials,
    isnull(person.title, '' ) as title,
    isnull(person.firstname, '') as firstname,
    isnull(person.lastname, '') as lastname,
    isnull(phone.phone, '') as phonenr,
    isnull(phone1.phone, '') as ext,
    isnull(phone2.phone, '') as mobilenr,
    isnull(email.email_address, '') as emailaddress
from crm5.contact contact
    left join crm5.address address on contact.contact_id = address.owner_id and address.atype_idx = 2
    left join crm5.person person on contact.contact_id = person.contact_id
    left join crm5.phone phone on contact.contact_id = phone.owner_id and phone.ptype_idx = 1
    left join crm5.phone phone1 on person.person_id = phone1.owner_id and phone1.ptype_idx = 16385
    left join crm5.phone phone2 on person.person_id = phone2.owner_id and phone2.ptype_idx = 16389
    left join crm5.email email on person.person_id = email.person_id and email.rank = 1
WHERE contact.[name] = REPLACE(@name,'\'','')
order by contact.[name], firstname, lastname
GO

Regel 25 en 26 worden rood

[ Voor 8% gewijzigd door DJ Chicken op 16-05-2006 15:05 ]

[whoami]

Quotes escape je in Sql Server niet mbhv \ maar dmv dubbele quotes.

Echter, dat is geen oplossing voor je probleem, slechts een work-around. Normaal gezien moeten de parameters alles goed afhandlen.

Inderdaad dus die 's in de zoekstring.

Zou je zo moeten noteren in Sql:

code:

'''s Heren... '

Echter, als je die parameters goed gebruikt, zou dat voor jou automatisch moeten gedaan worden; vandaar mijn vraag: kijk eens mbhv de profiler wat er precies naar je DB gestuurd wordt.

[ Voor 43% gewijzigd door whoami op 16-05-2006 15:10 ]

[_js_]

SQL:

where contact.[name] = '''s Heeren Loo Zorggroep'

en zo? Krijg je dan wel resultaten terug?

En in je applicatie, krijg je een error of geen resultaten?

Escapen van quotes in t-sql doe je door er nog een quote voor te zetten, dus replace(@name,'''','')

[TheRookie]

offtopic:
[quote]whoami schreef op dinsdag 16 mei 2006 @ 15:07:
Quotes escape je in Sql Server niet mbhv \ maar dmv dubbele quotes.[/quote] my bad, moet ook niet zoveel in php coden

Als de manier van _js_ geen resultaten oplevert zou je ook ff met de query analyser in de tabel kunnen kijken of het record bestaat en hoe de betreffende kolom door de applicatie gevuld wordt; misschien is er door de ontwikkelaars voor een niet-standaard manier gekozen om met ' om te gaan ?

[DJ Chicken]

Ik ben er uit denk ik, simpel en irritant db puntje.
Als ik zo tijd heb licht ik het wel ff toe.

[DJ Chicken]

Ik heb ff een tweetal screenshots gemaakt van wat nu psies het probleem werkelijk bleek te zijn.
Het heeft niets met de stored procedure te maken of de parameter erin.

Op het 1e screenshot kan je zien dat een bepaalde naam is opgeslagen in de db met een dubbele spatie in de naam.
Op het 2e screenshot is te zien hoe het DataGrid deze dubbele spatie netjes verwijderd en er een enkele weergeeft.

Jammer is nu dat deze kolom net een HyperlinkColumn is die de naam doorgeeft aan de parameter in de stored procedure, en de naam met de enkele spatie dus niet wordt herkend. Daarom kreeg ik dus een foutmelding en geen resultaat.







Nu is mij vraag dus wel, hoe krijg ik voor elkaar dat het DataGrid de exacte benaming overneemt, dus niet de dubbele spatie verwijderd.

[sig69]

Dat je tweede spatie verwijdert wordt is volgens mij een standaard feature van html. Misschien dat je dit op kan lossen door er <pre> </pre> tags omheen te zetten? (Ben niet zo'n html goeroe...) Sowieso zo het netter zijn om bijvoorbeeld het id van het bedrijf in de link te zetten.

[hamsteg]

Twee spaties kunnen niet in HTML. Een oplossing: vervang vlak voor weergave [space] met & nbsp; (Non Breakable Space, spatie tussen & en nbsp weghalen ... helaas intepreteert dit forum hem ook)

[ Voor 26% gewijzigd door hamsteg op 17-05-2006 13:29 ]

[DJ Chicken]

Helaas zijn de 2 voorgaand post geen oplossing omdat ik met ASPX datagrid werkt.

Screenshot 2 toont geen gewone tabel maar een DataGrid wat ik aanmaak in C# mbv boundcolumns en hyperlinkcolumns. Die worden dynamisch aangemaakt wanneer er gegevens worden opgevraagd.
Dit alles is een Web Part in een SharePoint Portal, dus helaas geen HTML gerommel :-).

Ik zoek nu de eigenschap dat de DataGrid de exacte naam overneemt zonder de spatie te verwijderen.

[sig69]

In een datagrid kan je gewoon het itemtemplate aanpassen toch? Of een templatecolumn maken met een hyperlink erin. M'n .net is een beetje roestig...

[DJ Chicken]

sig69 schreef op woensdag 17 mei 2006 @ 13:41:
In een datagrid kan je gewoon het itemtemplate aanpassen toch? Of een templatecolumn maken met een hyperlink erin. M'n .net is een beetje roestig...

Een templatecolumn maken met een hyperlink erin is geen optie in dit geval, voor mijn functionalitiet heb ik een echte HyperlinkColumn nodig zoals ik nu gebruik.

Ik kan wel kijken of ik het itemtemplate ervan kan aanpassen.

[gorgi_19]

DJ Chicken schreef op woensdag 17 mei 2006 @ 14:51:
Een templatecolumn maken met een hyperlink erin is geen optie in dit geval, voor mijn functionalitiet heb ik een echte HyperlinkColumn nodig zoals ik nu gebruik.

Afaik is een templatecolumn vele malen flexibeler dan een HyperlinkColumn, welke functionaliteit mis je dan?

[DJ Chicken]

gorgi_19 schreef op woensdag 17 mei 2006 @ 14:55:
[...]



Afaik is een templatecolumn vele malen flexibeler dan een HyperlinkColumn, welke functionaliteit mis je dan?

Dit is mijn hyperlinkcolumn, er zit javascript in die de hyperlink meeneemt in een stored procedure:

C#:

newColumn_Bedrijfsnaam = new HyperLinkColumn();
            newColumn_Bedrijfsnaam.DataTextField = "name";
            newColumn_Bedrijfsnaam.DataNavigateUrlField = "name";
            newColumn_Bedrijfsnaam.DataNavigateUrlFormatString ="javascript:void window.open('_layouts/customforms/bRapportage.aspx?name={0}',null,'top=300; left=1; width=1200; height=140; scrollbars=1; resizable=0')";
            newColumn_Bedrijfsnaam.HeaderText = "Bedrijfsnaam:";
            
            newColumn_Bedrijfsnaam.DataTextFormatString="{0,0}";
            
            newColumn_Bedrijfsnaam.HeaderStyle.Width=120;

Is dit dan wel mogelijk in een templatecolumn? En lost dat dan gelijk mijn bovenstaande probleem op(te zien in de screenshots)

Zelf zou ik niet weten hoe ik hievan een templatecolumn moet maken, ik heb wel ASPX pagina voorbeeldne gevondne maar in mijn webpart staat het net wat anders zoals je kan zien.

[ Voor 25% gewijzigd door DJ Chicken op 17-05-2006 15:15 ]

[sig69]

DJ Chicken schreef op woensdag 17 mei 2006 @ 15:08:
[...]
Is dit dan wel mogelijk in een templatecolumn?

Tuurlijk. Een Hyperlink heeft ook gewoon een NavigateUrl property etc.

En lost dat dan gelijk mijn bovenstaande probleem op(te zien in de screenshots)

Durf ik niet te zeggen, maar zoals ik al eerder zei is het netter om het ID mee te geven.

[DJ Chicken]

sig69 schreef op woensdag 17 mei 2006 @ 15:31:
[...]

Tuurlijk. Een Hyperlink heeft ook gewoon een NavigateUrl property etc.

[...]

Durf ik niet te zeggen, maar zoals ik al eerder zei is het netter om het ID mee te geven.

Ok dat ID meegeven ga ik doen ja...stom van me dat ik hem op naam pak.

Maar wat je bedoeld met

Tuurlijk. Een Hyperlink heeft ook gewoon een NavigateUrl property etc.

weet ik niet psies, omdat ik nog nooit een TemplateColumn heb gemaakt. En voor webparts is het een tikketle afwijkend in vergelijking met een ASPX pagina.

[ Voor 4% gewijzigd door DJ Chicken op 17-05-2006 16:41 ]

[DJ Chicken]

Ik heb nu gewoon het ID mee gegeven en dat heeft het probleem eigelijk al opgelost.
In de templatecolumns ga ik me nog wel ff verder verdiepen, die klinken handig.