syntax question

[mcnewsxp]

how would i code a replcae statement in this line of code where i need to remove the apostrophe from t2.firstname?
i do not have the option to rewrite anything. just need to make this work.

sql += "and t2.lastname + t2.firstname + t2.address1 + t2.city + t2.state + t2.zip = ";

tia,
mcnewsxp

 

[Arne Vajhøj]

how would i code a replcae statement in this line of code where i need to remove the apostrophe from t2.firstname?
i do not have the option to rewrite anything. just need to make this work.

sql += "and t2.lastname + t2.firstname + t2.address1 + t2.city + t2.state + t2.zip = ";

You should use parameters.

If that is not an option then try to get assigned to a different
project!

If you like putting band aid on bad code, then look
at String Replace.

Arne

 

[mcnewsxp]

You should use parameters.



If that is not an option then try to get assigned to a different

project!



If you like putting band aid on bad code, then look

at String Replace.



Arne

this is what i did:
string lastname = " replace(t2.lastname, " + ((char)(34)).ToString() + ((char)(39)).ToString() + ((char)(34)).ToString() + ((char)(44)).ToString() +((char)(34)).ToString() + ((char)(39)).ToString() + ((char)(39)).ToString() + ((char)(34)).ToString() +")";

if we could all live in your perfect world....

 

[Matthew Wells]

I'm guessing you're passing this to SQL Server...

sql += "and t2.lastname + REPLACE(t2.firstname. char(39), '') + T2.Address1
+ T2.City + T2.State + T2.Zip = ";

"mcnewsxp" wrote in message

how would i code a replcae statement in this line of code where i need to
remove the apostrophe from t2.firstname?
i do not have the option to rewrite anything. just need to make this work.

sql += "and t2.lastname + t2.firstname + t2.address1 + t2.city + t2.state +
t2.zip = ";

tia,
mcnewsxp

 

[Arne Vajhøj]

this is what i did:
string lastname = " replace(t2.lastname, " + ((char)(34)).ToString() + ((char)(39)).ToString() + ((char)(34)).ToString() + ((char)(44)).ToString() + ((char)(34)).ToString() + ((char)(39)).ToString() + ((char)(39)).ToString() + ((char)(34)).ToString() +")";

It is not quite clear to me how that code relates to your first post.

My suggestion would be implemented something like:

sql += "and t2.lastname + t2.firstname.Replace("'", "") + t2.address1 +
t2.city + t2.state + t2.zip = ";

And ((char)(34)).ToString() would usually be written as "\"" or @"""".

Arne

 

[mcnewsxp]

I'm guessing you're passing this to SQL Server...



sql += "and t2.lastname + REPLACE(t2.firstname. char(39), '') + T2.Address1

+ T2.City + T2.State + T2.Zip = ";



"mcnewsxp" wrote in message




how would i code a replcae statement in this line of code where i need to

remove the apostrophe from t2.firstname?

i do not have the option to rewrite anything. just need to make this work.



sql += "and t2.lastname + t2.firstname + t2.address1 + t2.city + t2.state +

t2.zip = ";



tia,

mcnewsxp

yes.

 

[mcnewsxp]

It is not quite clear to me how that code relates to your first post.



My suggestion would be implemented something like:



sql += "and t2.lastname + t2.firstname.Replace("'", "") + t2.address1 +

t2.city + t2.state + t2.zip = ";



And ((char)(34)).ToString() would usually be written as "\"" or @"""".

the code builds a string (sql) that is passed to mssql server so it would not recognize the replace function. what i did works.
i was hoping that anyone who would answer my post would have seen code likethat before. pretty common way to code. you can't fix everything....

 

[mcnewsxp]

[...]

i was hoping that anyone who would answer my post would have seen code

like that before. pretty common way to code. you can't fix

everything....

All due respect, there's nothing "common" at all about the code examples

you posted. SQL is not "common" in the context of C#, but more important,

no one in their right mind encodes literal characters by casting an integer

to a char and then calling ToString() on the result.



At the very worst, one might use the \uXXXX escaping syntax in a C# string,

to deal with esoteric characters not available on the typical keyboard. But

for characters like the ones you're dealing with, which are part of the

original ASCII specification? One _normally_ (or if you like, "commonly")

just types them in!

i have commonly seen code that builds sql code in several programming languages over the years. even older programming books used to have examples like that. it is bad prcatice, but a lot of it still exists. and a lot of itdoes stuff that you don't want to break and don't have the time or have been asked not to fix.
so if you can show me how to do the age old apostrophe fix within that messi'd be obliged. else you may keep your comments for your students.

 

[bradbury9]

El martes, 18 de diciembre de 2012 14:37:03 UTC+1, mcnewsxp escribió:

[...]

i was hoping that anyone who would answer my post would have seen code

like that before. pretty common way to code. you can't fix

All due respect, there's nothing "common" at all about the code examples
you posted. SQL is not "common" in the context of C#, but more important,
no one in their right mind encodes literal characters by casting an integer
to a char and then calling ToString() on the result.
At the very worst, one might use the \uXXXX escaping syntax in a C# string,
to deal with esoteric characters not available on the typical keyboard.But
for characters like the ones you're dealing with, which are part of the
original ASCII specification? One _normally_ (or if you like, "commonly")
just types them in!

i have commonly seen code that builds sql code in several programming languages over the years. even older programming books used to have examples like that. it is bad prcatice, but a lot of it still exists. and a lot of it does stuff that you don't want to break and don't have the time or have been asked not to fix.

so if you can show me how to do the age old apostrophe fix within that mess

i'd be obliged. else you may keep your comments for your students.

First choice: Use parameterized queries.

Second choice: If I would be obligated to do that nasty way (and if I coulduse recent .NET framework) I would of a function to exclude those characters.

public string doSqlReplaces(string parameter)
{
char[] invalidCharacters = new char[] { '\'', '"', '\u00c0' }; // single quote, doble cuote, weird unicode character
string returnedValue = new string((from character in parameter where !invalidCharacters.Contains(character) select character).ToArray());
return returnedValue;
}

If I had to call that function from many places I would use and extension method.

public static string ToValidSql(this string input)
{
//similar code to previous function
}

string parameter = "this is some random string ' \" with characters I dont want";
string sql = "this is my select " + parameter.ToValidSql() + " and this is more of the select";


PS: There are ways to do bad practice without converting the code in a nightmare.

 

[mcnewsxp]

Building SQL code is common enough, but that's not the issue here. It's

the way the strings are being declared that is.






If you are using a book that tells you to cast integers and then call

ToString() just to get a character into a string that you could have typed

using the keyboard directly, then you need a better book. A _much_ better

book.










Here is the code you posted:



string lastname = " replace(t2.lastname, " +

((char)(34)).ToString() +((char)(39)).ToString() +

((char)(34)).ToString() + ((char)(44)).ToString() +

((char)(34)).ToString() + ((char)(39)).ToString() +

((char)(39)).ToString() + ((char)(34)).ToString() +")";



Here is the same code written without all the crazy casting+ToString()

calls:



string lastname = " replace(t2.lastname, \"'\",\"''\")";



The worst it gets is having to escape the " characters, because they are

used to delimit C# string literals themselves. The other characters, the

apostrophe and comma, those don't even need escaping.



As a bonus, you don't incur the run-time overhead of all those calls to

char.ToString() and string.Concat(). You just get a single literal string

interned as a constant in the code.



Pete

that makes sense.
thanks.