Re: embedded ' in strings
From: mgarner1980 (mgarner_at_kbsi.com)
Date: 07/16/04
- Next message: Dave Nettleton [MSFT]: "Re: select permission required on msdb..MSagent_profiles?"
- Previous message: Bruno Cunha e Silva: "rda pull error"
- In reply to: Brad Syputa - MS: "Re: embedded ' in strings"
- Next in thread: Brad Syputa - MS: "Re: embedded ' in strings"
- Reply: Brad Syputa - MS: "Re: embedded ' in strings"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 16 Jul 2004 10:00:09 -0500
Nope, you seem to have it right. I've been doing some more testing. Here's
what I've found. I have 2 tables that have a nchar(6) column.
Table1 has the column "badge" whic is included in several indexes. Any time
I try "select * from table1 where badge = (insert any string longer than
six characters). I get the error. I have some screen shots, but last time I
tried to attach some, my mail never went through.
table2 has a column named fman whic is nchar(6) , but is not included in any
indexes. I can perform a select query with a string of any length and not
get the error.
I've started thi s thread out with the assumption that I was having problems
with the single quote issue, but really I'n having a problem with select
queries with vaues longer than the column being queried (on some tables). I
might also add that this database is a merge subscription.
I just tried the same test on another table with nchar(2) and an index, and
it didn't fail with a 25 length query. So it looks like this problem is a
particular type of problem.
I guess I could send you the sqlce database so you can test it. (it's a
subset of our customers data, so I'll have to check on that).
""Brad Syputa - MS"" <bradsy@Online.Microsoft.com> wrote in message
news:qMx5D6oaEHA.2924@cpmsftngxa06.phx.gbl...
> I tried what you said and it works fine for me. Was there something I was
> missing?
>
> Create Table Foo (Col1 nchar(6));
> Insert Into Foo (Col1) Values ('123''45');
> Success.
>
>
> --------------------
> | From: "mgarner1980" <mgarner@kbsi.com>
> | References: <u4i30KfZEHA.3092@tk2msftngp13.phx.gbl>
> <j9UqnzDaEHA.3272@cpmsftngxa06.phx.gbl>
> | Subject: Re: embedded ' in strings
> | Date: Wed, 14 Jul 2004 18:01:04 -0500
> | Lines: 59
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <uwWvxZfaEHA.2516@TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.ce
> | NNTP-Posting-Host: 209.189.225.79
> | Path:
>
cpmsftngxa06.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08
> phx.gbl!TK2MSFTNGP10.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.ce:12855
> | X-Tomcat-NG: microsoft.public.sqlserver.ce
> |
> | Sorry should've followed up on this one. Here's what was happening. The
> | field I was trying to use the where clause on was a CHAR length 6.
> | So let's say the user types in 123'45. My function would turn it in to
> | 123''45. which is length 7. When I tried to execute the query I would
get
> | the null reference exception.
> | So for now if there's a ' I throw it out. it's either that or truncate
it
> to
> | six characters.
> | The thing that had me going was that the same queries worked fine insql
> | query analyzer for the desktop. BTW the same error also occures in th
> esqlce
> | query analyzer if you want to see what I'm talking about. It seems like
a
> | bogus error since once the "escape" is processed, the end result should
> be a
> | string length 6.
> |
> | ""Brad Syputa - MS"" <bradsy@Online.Microsoft.com> wrote in message
> | news:j9UqnzDaEHA.3272@cpmsftngxa06.phx.gbl...
> | > Hi mgarner,
> | >
> | > Can you explain a little better how it throws a null reference
> exception?
> | I
> | > us a double single quote and it works fine for me.
> | >
> | >
> | > --------------------
> | > | From: "mgarner1980" <mgarner@kbsi.com>
> | > | Subject: embedded ' in strings
> | > | Date: Fri, 9 Jul 2004 15:24:14 -0500
> | > | Lines: 13
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> | > | Message-ID: <u4i30KfZEHA.3092@tk2msftngp13.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.ce
> | > | NNTP-Posting-Host: 209.189.225.79
> | > | Path:
> | >
> |
>
cpmsftngxa06.phx.gbl!TK2MSFTNGXS01.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
> | > 8.phx.gbl!tk2msftngp13.phx.gbl
> | > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.ce:12805
> | > | X-Tomcat-NG: microsoft.public.sqlserver.ce
> | > |
> | > | I'm having problems with queries like the following in sqlserverce.
> | > |
> | > | select name from employee where lastname = 'O'Brien'
> | > |
> | > | If I use double single quote like O''Brien, that works fine on my
> | > sqlser2000
> | > | server, but throws a null reference error on sqlserverce merge
> | > | subscription.
> | > |
> | > | I've tried paramaterized queries with to no avail as well.
> | > |
> | > | Any ideas?
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>
- Next message: Dave Nettleton [MSFT]: "Re: select permission required on msdb..MSagent_profiles?"
- Previous message: Bruno Cunha e Silva: "rda pull error"
- In reply to: Brad Syputa - MS: "Re: embedded ' in strings"
- Next in thread: Brad Syputa - MS: "Re: embedded ' in strings"
- Reply: Brad Syputa - MS: "Re: embedded ' in strings"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
