How do I insert escape sequences using SQL Compact.
-
Simple question I know but I just cannot find a anything that works.
INSERT INTO [Location] ([id],[TZOffset],[Address],[Tel],[Fax],[Contact1],[Contact2],[Contact3],[Company],[Deleted],[Disabled]) VALUES (8,null,N'line1
line2
',N'',N'',1,2,3,null,null,null);
GOI have tried everything I can find but there is no ESCAPE or CHAR function in SQLCE that I can find. You cannot use Stored Procedures, being CE so it has to be done in a command space. If I store the CRLF in the raw data and push it using MFC then it works fine. Any ideas? Great isn't it. I can use SIN, COT, SQRT but I cannot put CRLF into a string!!?!??!?!
Alan
-
Simple question I know but I just cannot find a anything that works.
INSERT INTO [Location] ([id],[TZOffset],[Address],[Tel],[Fax],[Contact1],[Contact2],[Contact3],[Company],[Deleted],[Disabled]) VALUES (8,null,N'line1
line2
',N'',N'',1,2,3,null,null,null);
GOI have tried everything I can find but there is no ESCAPE or CHAR function in SQLCE that I can find. You cannot use Stored Procedures, being CE so it has to be done in a command space. If I store the CRLF in the raw data and push it using MFC then it works fine. Any ideas? Great isn't it. I can use SIN, COT, SQRT but I cannot put CRLF into a string!!?!??!?!
Alan
Me thinks your data structure is screwed, whenever I seelsomething like contact1, contact2, contact3 I know you are in deep poop. What if there is a fourth contact, woops lets add another column - FAIL Create another table callemd contacts and a foreign key to this company table. What has CRLF got to do with your insert statement? There seems to be a number od problems with your insert statement. There is no comma between line1 and line2. Do comapny, deleted and disabled accept null as valid data.
Never underestimate the power of human stupidity RAH
-
Me thinks your data structure is screwed, whenever I seelsomething like contact1, contact2, contact3 I know you are in deep poop. What if there is a fourth contact, woops lets add another column - FAIL Create another table callemd contacts and a foreign key to this company table. What has CRLF got to do with your insert statement? There seems to be a number od problems with your insert statement. There is no comma between line1 and line2. Do comapny, deleted and disabled accept null as valid data.
Never underestimate the power of human stupidity RAH
Well you would be right if that is the way the fields were being used but they are not. There will only be three contacts as these fields hold pointers to entries in a Contact table. I want to use a single ADDRESS field to store a multiline address rather than have 5 separate fields (one for each line). There is no comma between line1 & line2 becuase they are stored as "
line1\r\nline2" in the application internals. Now I can do that everywhere else (CString, Registry etc...) so why is it so unreasonable to want the same thing in SQL CE?Alan
-
Well you would be right if that is the way the fields were being used but they are not. There will only be three contacts as these fields hold pointers to entries in a Contact table. I want to use a single ADDRESS field to store a multiline address rather than have 5 separate fields (one for each line). There is no comma between line1 & line2 becuase they are stored as "
line1\r\nline2" in the application internals. Now I can do that everywhere else (CString, Registry etc...) so why is it so unreasonable to want the same thing in SQL CE?Alan
Not being a user of CE, and unwilling to install it in case it destabilises something, I can't test his. Have you tried forcing a linefeed manually by inserting chr(10). Then try chr(13) + chr(10), forcing a carriage return and line feed. Seems like your transport layer may be messing with the \r Thanks for the explanation (one should stick to the Lounge when one returns home pissed) but I still think field1-3 is an incorrect design.
Never underestimate the power of human stupidity RAH
-
Not being a user of CE, and unwilling to install it in case it destabilises something, I can't test his. Have you tried forcing a linefeed manually by inserting chr(10). Then try chr(13) + chr(10), forcing a carriage return and line feed. Seems like your transport layer may be messing with the \r Thanks for the explanation (one should stick to the Lounge when one returns home pissed) but I still think field1-3 is an incorrect design.
Never underestimate the power of human stupidity RAH
Ok things are getting even wierder than I thought they were to start with. I have done some testing with my own code and I have observed that INSERT preserves the 0x000D,0x000A when written to the DB but then if I use UPDATE on the same field with the same data I get '?' stored in the DB?!?!?!? Now this smells to me of a bug in the OLEDB... Anway it looks like I am going to have to restructure my code and DB to provide separate fields for each address line. I then have to glue them together when I retrieve them and split them apart when writing. You are still getting hung up on the Contact1/2/3. This is simple the variable names I use within the code so I can easily find all the code dealing with the contacts. In presentation they are Primary, Secondary and Reserve. Simples. :)
Alan
-
Ok things are getting even wierder than I thought they were to start with. I have done some testing with my own code and I have observed that INSERT preserves the 0x000D,0x000A when written to the DB but then if I use UPDATE on the same field with the same data I get '?' stored in the DB?!?!?!? Now this smells to me of a bug in the OLEDB... Anway it looks like I am going to have to restructure my code and DB to provide separate fields for each address line. I then have to glue them together when I retrieve them and split them apart when writing. You are still getting hung up on the Contact1/2/3. This is simple the variable names I use within the code so I can easily find all the code dealing with the contacts. In presentation they are Primary, Secondary and Reserve. Simples. :)
Alan
Instead of multiple fields for the address you might look into storing it as XML data or varchar using XML to structure the address.
Never underestimate the power of human stupidity RAH