Help with Special Characters in Updategram

I'm looking for help and hope you can provide it. I've got an
updategram with special ISO Latin 1 Characters like begin and end
quotes #147 and #148 or copyright character #169. The UpdateGram fails
with the message "400.100 Bad Request, Query not specified". The
UpdateGram works when the characters are removed. If I try to HTML
encode the characters (“ ” or ©) then the updategram
saves but the characters are replaced with '?'.
Any ideas how to get around this?Hi Todd,
The error message you are seeing is not coming from the Updagram component
and is coming from some other layer of the web application. How are you
sending this to the database? One way to check if something is wrong with
the Updategram is to try to send the Updategram directly through ADO or
managed provider. If that does not work and you see an error then please
post a snippet of the Updategram that you think is causing the problem.
Thank you,
Amar Nalla [MSFT]
PS: This posting is provided AS IS, and confers on rights or warranties.
"Todd" <todd_gochenour@.msn.com> wrote in message
news:1110836825.988706.296230@.z14g2000cwz.googlegroups.com...
> I'm looking for help and hope you can provide it. I've got an
> updategram with special ISO Latin 1 Characters like begin and end
> quotes #147 and #148 or copyright character #169. The UpdateGram fails
> with the message "400.100 Bad Request, Query not specified". The
> UpdateGram works when the characters are removed. If I try to HTML
> encode the characters (“ ” or ©) then the updategram
> saves but the characters are replaced with '?'.
> Any ideas how to get around this?
>|||The updategram is posted to the SQLXML website. The content of the
updategram is:
<?xml version=3D"1.0" encoding=3D"UTF-8"?>
<ROOT xmlns:updg=3D"urn:schemas-microsoft-com:xml-updategram">
<updg:sync>
<updg:before>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test"/>
</updg:before>
<updg:after>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test" VALUE=3D"Tomorrow's Hope=A9"/>
</updg:after>
</updg:sync>
</ROOT>
Note the curly apostrophe and the copyright symbol. If they are
removed, then the updategram is successful.|||Also, I have not attempted to submit this updategram through the ADO
interface. It'll take some work to get this setup, and my current
configuration is using the HTTP based interface.

Help with Special Characters in Updategram

I'm looking for help and hope you can provide it. I've got an
updategram with special ISO Latin 1 Characters like begin and end
quotes #147 and #148 or copyright character #169. The UpdateGram fails
with the message "400.100 Bad Request, Query not specified". The
UpdateGram works when the characters are removed. If I try to HTML
encode the characters (“ ” or ©) then the updategram
saves but the characters are replaced with '?'.
Any ideas how to get around this?
Hi Todd,
The error message you are seeing is not coming from the Updagram component
and is coming from some other layer of the web application. How are you
sending this to the database? One way to check if something is wrong with
the Updategram is to try to send the Updategram directly through ADO or
managed provider. If that does not work and you see an error then please
post a snippet of the Updategram that you think is causing the problem.
Thank you,
Amar Nalla [MSFT]
PS: This posting is provided AS IS, and confers on rights or warranties.
"Todd" <todd_gochenour@.msn.com> wrote in message
news:1110836825.988706.296230@.z14g2000cwz.googlegr oups.com...
> I'm looking for help and hope you can provide it. I've got an
> updategram with special ISO Latin 1 Characters like begin and end
> quotes #147 and #148 or copyright character #169. The UpdateGram fails
> with the message "400.100 Bad Request, Query not specified". The
> UpdateGram works when the characters are removed. If I try to HTML
> encode the characters (“ ” or ©) then the updategram
> saves but the characters are replaced with '?'.
> Any ideas how to get around this?
>
|||The updategram is posted to the SQLXML website. The content of the
updategram is:
<?xml version=3D"1.0" encoding=3D"UTF-8"?>
<ROOT xmlns:updg=3D"urn:schemas-microsoft-com:xml-updategram">
<updg:sync>
<updg:before>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test"/>
</updg:before>
<updg:after>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test" VALUE=3D"Tomorrow's Hope=A9"/>
</updg:after>
</updg:sync>
</ROOT>
Note the curly apostrophe and the copyright symbol. If they are
removed, then the updategram is successful.
|||Also, I have not attempted to submit this updategram through the ADO
interface. It'll take some work to get this setup, and my current
configuration is using the HTTP based interface.

Help with retrieving string data

Hello,

I am trying to retrieve only the first few characters (12 to be precise) from this string that is coming in from FoxPro to SQL Server 2005 and I am coding in C#. I have tried these methods (after reading it in a book, as I am new to this) but it still gives me an error saying that the field cannot exceed 12 characters.

autoClaimSalvage.Phone = "Select LEFT ('" + (string)(drInputDataSource["OWNPHN"]) + "',12) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM(OWNPHN)) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM('" + ((string)(drInputDataSource["OWNPHN"])) + "'))" + entityManager.TargetTable + "'";

Please let me know what i am doing wrong and if anyone has a sample code or if you can point me in the right direction, I will appreciate it.

Thanks for your help in advance.

Can you share/check the length of 'ownph' column in the database. Your second select is doing ltrim(rtrim) but not limiting the length to 12 characters.

|||

I am not sure how long the field is in the database that I am retrieving the data from, because I get nothing when I put a watch on the field. As to the ltrim(rtrim) function, the examples do not show how to limit the length to any specified number of characters. All the examples only show to do ltrim(rtrim(fieldname)).

|||Problem solved. Thank you for your help.|||As we use this post to learn new things, can you share how you got around the problem.

Help with retrieving string data

Hello,

I am trying to retrieve only the first few characters (12 to be precise) from this string that is coming in from FoxPro to SQL Server 2005 and I am coding in C#. I have tried these methods (after reading it in a book, as I am new to this) but it still gives me an error saying that the field cannot exceed 12 characters.

autoClaimSalvage.Phone = "Select LEFT ('" + (string)(drInputDataSource["OWNPHN"]) + "',12) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM(OWNPHN)) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM('" + ((string)(drInputDataSource["OWNPHN"])) + "'))" + entityManager.TargetTable + "'";

Please let me know what i am doing wrong and if anyone has a sample code or if you can point me in the right direction, I will appreciate it.

Thanks for your help in advance.

Can you share/check the length of 'ownph' column in the database. Your second select is doing ltrim(rtrim) but not limiting the length to 12 characters.

|||

I am not sure how long the field is in the database that I am retrieving the data from, because I get nothing when I put a watch on the field. As to the ltrim(rtrim) function, the examples do not show how to limit the length to any specified number of characters. All the examples only show to do ltrim(rtrim(fieldname)).

|||Problem solved. Thank you for your help.|||As we use this post to learn new things, can you share how you got around the problem.

Help with query that only returns filds with certain characters

Hi,
I need to write a sql statement that only returns records if
a certain field DOESN'T contain a letter between a and d, a number, a #,
and a asterisk. If a different character is found there, it should
return that record.
For example. If we have these records
1. 43242#
2. %3499
3. $$#
4. ak
5. abd43#
6. 4242#44z
7. abc_
8. 342#ab*
9. *(
My query should return 2, 3, 4, 6, 7 and 9
I tried the following:
select * from Table1
where MyField LIKE '%[^0-9]%'
and MyField like '%[^a-d]%'
and it works fine for the numbers and letters, but I don't how to
include the # and the *
Thanks a lot.You can negate the LIKE clause by putting NOT in front of it.
So
SELECT * FROM Table1
WHERE MyField LIKE '%[^0-9]%'
AND MyField LIKE '%[^a-d]%'
AND MyField NOT LIKE '%#%'
AND MyField NOT LIKE '%*%'
One note though, the criteria you mentioned and the examples you gave do not
seem to match up.

But you said you should return rows (and your sample code that you said
works as intended) that do NOT contain a number. Some of those rows clearly
contain a number. Just not sure where you were going with that.
HTH,
John Scragg
"Star" wrote:
> Hi,
> I need to write a sql statement that only returns records if
> a certain field DOESN'T contain a letter between a and d, a number, a #,
> and a asterisk. If a different character is found there, it should
> return that record.
> For example. If we have these records
> 1. 43242#
> 2. %3499
> 3. $$#
> 4. ak
> 5. abd43#
> 6. 4242#44z
> 7. abc_
> 8. 342#ab*
> 9. *(
>
> My query should return 2, 3, 4, 6, 7 and 9
> I tried the following:
> select * from Table1
> where MyField LIKE '%[^0-9]%'
> and MyField like '%[^a-d]%'
> and it works fine for the numbers and letters, but I don't how to
> include the # and the *
> Thanks a lot.
>|||John,
Yes, I think my explanation was a little bit confusing.
I will try to rephrase it.
The query should return rows if the field
does not contain one of these characters:
- Numbers
- Letters (a-d)
- #
- *
For example, the query should return %3499 because
there is a % symbol there and that symbol is not on that list.
I had already tried what you suggested, but doesn't work for me.
If I run it, I wouldn't get 4242#44z back, and I should because it
contains a 'z'
John Scragg wrote:
> You can negate the LIKE clause by putting NOT in front of it.
> So
> SELECT * FROM Table1
> WHERE MyField LIKE '%[^0-9]%'
> AND MyField LIKE '%[^a-d]%'
> AND MyField NOT LIKE '%#%'
> AND MyField NOT LIKE '%*%'
> One note though, the criteria you mentioned and the examples you gave do n
ot
> seem to match up.
>
>
> But you said you should return rows (and your sample code that you said
> works as intended) that do NOT contain a number. Some of those rows clear
ly
> contain a number. Just not sure where you were going with that.
> HTH,
> John Scragg
>
> "Star" wrote:
>|||
You may also need to consider the escape character
e.g.
select * from (select 'a%b' col1 union select 'cde') x
where col1 LIKE '%\%%' ESCAPE ''
returns only 'a%b'|||Thanks, Steven. I will keep in mind.
However, I still haven't found a solution for this problem...
If I do this
select myfield from mytable
where myfield LIKE '%[^0-9]%'
and myfield like '%[^a-d]%'
and myfield not LIKE '%*%'
and myfield not LIKE '%#%'
and myfield LIKE '%\%%' ESCAPE ''
I only get %3499 back.
I really don't know what else to try...|||> I really don't know what else to try...
I've finally worked out (I think) what it is that you need
All records that contain one (or more) characters that are not in
(1234567890abcd#*)
Would that be a fair assumption ?|||
> I really don't know what else to try...
Is this SQL 2000 or SQL2005 ?
You could use a regex match
OR:
select col1 , patindex('%[^abcd1234567890#*]%',col1)
from
(
select
'43242#' col1
UNION SELECT
'%3499' UNION SELECT
'$$#' UNION SELECT
'ak' UNION SELECT
'abd43#' UNION SELECT
'4242#44z' UNION SELECT
'abc_' UNION SELECT
'342#ab*' UNION SELECT
'*('
) x
where patindex('%[^abcd1234567890#*]%',col1) <>0|||On Wed, 02 Nov 2005 14:05:46 -0500, Star wrote:

>John,
>Yes, I think my explanation was a little bit confusing.
>I will try to rephrase it.
>The query should return rows if the field
>does not contain one of these characters:
>- Numbers
>- Letters (a-d)
>- #
>- *
>For example, the query should return %3499 because
>there is a % symbol there and that symbol is not on that list.
Hi Star,
You write "does not contain one of these characters", but your example
suggests that you mean "contains at least one character not in this
list". For '%3499' does contain a number (even four!), yet you want it
returned.
SELECT MyField, other columns
FROM MyTable1
WHERE MyField LIKE '%[^0-9a-d#*]%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes... I apologize again. I had a really bad day. I should have thought
twice my question.
Sorry about that and thanks for your help.|||Awesome!
That worked. To be honest, I didn't know about that patindex function. I
won't forget next time.
I really really appreciate your help and time.