carriage return in a text field

We are in the process of updating a Text column.
How can we update the row and column and add a carriage return to the
existing text?A carriage return character is an ASCII 13 character. So if you wanted
foo
bar
then you would say
select 'foo' + char(13) + 'bar'
However, in the Windows world, carriage returns are normally paired with
line feed characters (Unix apps typically just have the CR with no LF).
A line feed character is ASCII 10. So a CR/LF pair would be
select 'foo' + char(13) + char(10) + 'bar'
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
wnfisba wrote:

>We are in the process of updating a Text column.
>How can we update the row and column and add a carriage return to the
>existing text?
>

Carriage return /New line in a Text field

Hi
Is there any way to force a carriage return/new line when inserting some
data into a text field?
I'm updating a text field with data from other fields, but in order to make
it look nice on a printout, I'd like to split it onto two lines. I've looked
in BOL, but haven't really been able to find anything that seems to be able
to do it.
Regards
Steen
Hi Steen,
Please check CHAR in SQL BOL.
I believe CHAR(13) is what you are looking for.
Cheers,
Des
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u7O7RBvmEHA.3988@.tk2msftngp13.phx.gbl...
> Hi
> Is there any way to force a carriage return/new line when inserting some
> data into a text field?
> I'm updating a text field with data from other fields, but in order to
> make
> it look nice on a printout, I'd like to split it onto two lines. I've
> looked
> in BOL, but haven't really been able to find anything that seems to be
> able
> to do it.
> Regards
> Steen
>
|||Yes - that looks like what I was looking for.
Thanks a lot...
Regards
Steen
Des FitzGerald wrote:[vbcol=seagreen]
> Hi Steen,
> Please check CHAR in SQL BOL.
> I believe CHAR(13) is what you are looking for.
> Cheers,
> Des
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u7O7RBvmEHA.3988@.tk2msftngp13.phx.gbl...
|||You may actually need to use a combination of char(13) +
char(10) depending on your settings. Windows apps
sometimes look for both (Carriage return/linefeed). UNIX
based systems generally only look for the one control
character (Carriage return is a carriage return, line
feed feeds a line but doesn't change your horizontal
position).
Just a thought.
Matthew Bando
Mattehw.Bando@.remove csctgi.com
[vbcol=seagreen]
>--Original Message--
>Yes - that looks like what I was looking for.
>Thanks a lot...
>
>Regards
>Steen
>Des FitzGerald wrote:
when inserting[vbcol=seagreen]
fields, but in order[vbcol=seagreen]
two lines. I've[vbcol=seagreen]
that seems to
>
>.
>

Carriage return /New line in a Text field

Hi
Is there any way to force a carriage return/new line when inserting some
data into a text field?
I'm updating a text field with data from other fields, but in order to make
it look nice on a printout, I'd like to split it onto two lines. I've looked
in BOL, but haven't really been able to find anything that seems to be able
to do it.
Regards
SteenHi Steen,
Please check CHAR in SQL BOL.
I believe CHAR(13) is what you are looking for.
Cheers,
Des
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u7O7RBvmEHA.3988@.tk2msftngp13.phx.gbl...
> Hi
> Is there any way to force a carriage return/new line when inserting some
> data into a text field?
> I'm updating a text field with data from other fields, but in order to
> make
> it look nice on a printout, I'd like to split it onto two lines. I've
> looked
> in BOL, but haven't really been able to find anything that seems to be
> able
> to do it.
> Regards
> Steen
>|||Yes - that looks like what I was looking for.
Thanks a lot...
Regards
Steen
Des FitzGerald wrote:
> Hi Steen,
> Please check CHAR in SQL BOL.
> I believe CHAR(13) is what you are looking for.
> Cheers,
> Des
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u7O7RBvmEHA.3988@.tk2msftngp13.phx.gbl...
>> Hi
>> Is there any way to force a carriage return/new line when inserting
>> some data into a text field?
>> I'm updating a text field with data from other fields, but in order
>> to make
>> it look nice on a printout, I'd like to split it onto two lines. I've
>> looked
>> in BOL, but haven't really been able to find anything that seems to
>> be able
>> to do it.
>> Regards
>> Steen|||You may actually need to use a combination of char(13) +
char(10) depending on your settings. Windows apps
sometimes look for both (Carriage return/linefeed). UNIX
based systems generally only look for the one control
character (Carriage return is a carriage return, line
feed feeds a line but doesn't change your horizontal
position).
Just a thought.
Matthew Bando
Mattehw.Bando@.remove csctgi.com
>--Original Message--
>Yes - that looks like what I was looking for.
>Thanks a lot...
>
>Regards
>Steen
>Des FitzGerald wrote:
>> Hi Steen,
>> Please check CHAR in SQL BOL.
>> I believe CHAR(13) is what you are looking for.
>> Cheers,
>> Des
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:u7O7RBvmEHA.3988@.tk2msftngp13.phx.gbl...
>> Hi
>> Is there any way to force a carriage return/new line
when inserting
>> some data into a text field?
>> I'm updating a text field with data from other
fields, but in order
>> to make
>> it look nice on a printout, I'd like to split it onto
two lines. I've
>> looked
>> in BOL, but haven't really been able to find anything
that seems to
>> be able
>> to do it.
>> Regards
>> Steen
>
>.
>|||Steen Persson wrote:
> *Hi
> Is there any way to force a carriage return/new line when inserting
> some
> data into a text field?
> I'm updating a text field with data from other fields, but in order
> to make
> it look nice on a printout, I'd like to split it onto two lines. I've
> looked
> in BOL, but haven't really been able to find anything that seems to
> be able
> to do it.
> Regards
> Steen *
fmutale02
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message393908.html|||Use a string funtion to select the fisrt part of a string and separate it
with a char(13) or concatenate multiple fileds with the char(13) in between.
Such as
insert into table
select left(field1,10)+char(13)+right(field1,10) from table
or
insert into table
select field1+char(13)+field2
"fmutale02" wrote:
> Steen Persson wrote:
> > *Hi
> >
> > Is there any way to force a carriage return/new line when inserting
> > some
> > data into a text field?
> >
> > I'm updating a text field with data from other fields, but in order
> > to make
> > it look nice on a printout, I'd like to split it onto two lines. I've
> > looked
> > in BOL, but haven't really been able to find anything that seems to
> > be able
> > to do it.
> >
> > Regards
> > Steen *
>
> --
> fmutale02
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message393908.html
>|||Some printers may need both char(13)+char(10) to cause a linefeed.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Jeff Ericson" <JeffEricson@.discussions.microsoft.com> wrote in message
news:358376A2-847C-4108-B5AD-3E615E8A7BB9@.microsoft.com...
> Use a string funtion to select the fisrt part of a string and separate it
> with a char(13) or concatenate multiple fileds with the char(13) in
> between.
> Such as
> insert into table
> select left(field1,10)+char(13)+right(field1,10) from table
> or
> insert into table
> select field1+char(13)+field2
>
>
> "fmutale02" wrote:
>> Steen Persson wrote:
>> > *Hi
>> >
>> > Is there any way to force a carriage return/new line when inserting
>> > some
>> > data into a text field?
>> >
>> > I'm updating a text field with data from other fields, but in order
>> > to make
>> > it look nice on a printout, I'd like to split it onto two lines. I've
>> > looked
>> > in BOL, but haven't really been able to find anything that seems to
>> > be able
>> > to do it.
>> >
>> > Regards
>> > Steen *
>>
>> --
>> fmutale02
>> ---
>> Posted via http://www.webservertalk.com
>> ---
>> View this thread: http://www.webservertalk.com/message393908.html
>>

Cant Update, Insert, or Delete rows

I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help.

I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types.

I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed.

I may upload a copy of the exact exception page, if someone thinks that it may help.

Here is the update command that was generated:

UPDATE [Record Information] SET [Speed] = @.Speed, [Recording Company] = @.Recording_Company, [Year] = @.Year, [Artist] = @.Artist, [Side 1 Track Title] = @.Side_1_Track_Title, [Side 1 Track Duration] = @.Side_1_Track_Duration, [Side 2 Track Title] = @.Side_2_Track_Title, [Side 2 Track Duration] = @.Side_2_Track_Duration, [Sleeve Description] = @.Sleeve_Description WHERE [Record Database ID] = @.original_Record_Database_ID

Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.

What I recommend is that you click the Learn link at the top of the page and go through some of the videos or quickstart tutorials to familiarise yourself with how the SqlDataSource works. It doesn't, for example, generate stored procedures under any circumstances - which is why you can't find any. You also need to understand what Optimistic Concurrency is, how to manage it and when to use it. Finally, you should understand what datatypes are the most appropriate for your data. Having nothing but ints and text datatypes is not very likely to be appropriate.

Cant update table

I'm having trouble updating a table in a SQL 2000 database usinb VB6.0. I have no problem opening the table and reading data from a field however when I go to update it I get the following error.

Runtimer error '-217418113 (8000ffff):
Query cannot be updated because the FROM clause is not a single simple table name.

I've tried to help myself with this one but any information which I found referred to someone trying to write to two tables simultaneously. I am simply trying to write to one.

Here's my code

Dim cnn1 As ADODB.Connection
Dim rstRailSet As ADODB.Recordset

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=" & ServerName & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=" & DBName & ";"
cnn1.Open
Set rstRailSet = New ADODB.Recordset

rstRailSet.LockType = adLockOptimistic

rstRailSet.Open "Select SLN From [Rail Set] where Rail_set_ID = '" & RailID & "'", cnn1, , , adCmdText
rstRailSet!SLN = RailSLN
rstRailSet.Update

The above message appears when the update command is executed.

Any help is greatly appreciated.maybe i am not an ADO expert but I do not see an update statement here. I see a recordset and a select statement.|||The update is done in the last line of code "rstRailSet.Update". I assume this is all that's required.|||This might be better posted in the VB forum.

gotcha. I had forgtten the rs object had an update command. I just always use either a command object or a con.execute(sql) where sql is an update sql command.

I might try this below.

rstRailSet.Open "Select SLN From [Rail Set] where Rail_set_ID = '" & RailID & "'", cnn1, , , adCmdText
rstRailSet("SLN") = RailSLN
rstRailSet.Update

But in your code snippt I do not see where RailSLN is defined or populated. Also according to some yellowing ADO 2.6 documentation on my bookshelf your datasource has to allow bookmarks and and keyset or dynamic cursors.|||Is [Rail Set] a table, or could it be a view?|||Thrasymachus

Tried your suggestion without luck. RailSLN is an argument passed to the sub containing this code. (I excluded it to keep it simple, or so I thought.)

Not sure of the bookmark/dynamic cursor implication. The source is a table within a SQL server 2000 database. I thought that the source of this issue may lie on the SQL server end hence I posted it here.

Blindmand

[Rail Set] is a table in a SQL server 2000 database.

Bare in mind here that I have no problem reading data from the source. The message seems to indicate that it some how can't work its way back. As these things can go I'm not sure whether the message is legit or whether its being triggered by an unrelated issue. (you know how this can go). It occurs when rstRailSet.Update is executed.|||SUCCESS

I set the cursor type to adOpenDynamic and I'm off to the races

rstRailSet.Open "Select SLN From [Rail Set] where Rail_set_ID = '" & RailID & "'", cnn1, adOpenDynamic, , adCmdText

At his point I do not understand enough about cursor type to know why it works but I'll bone up on it to satisfy my curiosity.

Thanks for your suggestions!