How to Insert / Retrieve Multi line Text

[kenth21v]

Good Day. I am using VB.net 2010 and SQL client server.
I have to make a Weekly Report Manager that stores an Trainees'
weekly activity, which would probably be in a paragraph form.

I think I can use Textbox (multilined) or Richtextbox for this.
But When I tried to store and read the stored data it is not multilined.

Example:

INPUT in textbox/richtextbox: I am
a good
trainee
inserted it in database as textbox.text or richtextbox.text
and the result will be:
I ama goodtrainee

So if I retrieve the data it displays a single lined text.

How can I do it properly? Please help me.
Thanks!

[Reverend Jim]

You have to show us what code you have in order for us to see where it is going wrong.

[codeorder]

Since I'm Not a db.coder, I do not know of any other way to pursue this other than:

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        With TextBox1 : .Text = replaceNewLine(.Text, True) : End With '// replace line.break w/String.
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        With TextBox1 : .Text = replaceNewLine(.Text, False) : End With '// replace String with line.break.
    End Sub

    Private Function replaceNewLine(ByVal selContent As String, ByVal isReplacingNewLineWithChar As Boolean, _
                                    Optional ByVal selNewLineStringToUse As String = ".:.myCooLvbNewLine.:.") As String
        If isReplacingNewLineWithChar Then : Return selContent.Replace(vbNewLine, selNewLineStringToUse)
        Else : Return selContent.Replace(selNewLineStringToUse, vbNewLine)
        End If
    End Function
End Class

Basically, replace each line.break w/a char. or a string that will not be found or likely used in your db.items, save to db and un.replace?:D the line.break string when loading items from the db.

[kenth21v]

my code is simple:

con.Open()
        cmd = New SqlCommand("INSERT INTO SampleTbl VALUES('" & TextBox1.Text & "'", con)
        cmd.ExecuteNonQuery()
        con.Close()

@codeorder sir i tried your code but I do not really understand it. if you may, can you explain it to me. tnx

[codeorder]

As previously mentioned, not a db.coder here, thus I'll edit w/no radar(my vb.net):D.

Dim sCoolNewLine As String = "~"
        con.Open()
        cmd = New SqlCommand("INSERT INTO SampleTbl VALUES('" & TextBox1.Text.Replace(vbNewLine, sCoolNewLine) & "'", con)
        cmd.ExecuteNonQuery()
        con.Close()

This TextBox1.Text.Replace(vbNewLine, sCoolNewLine) will replace line.breaks from a TextBox, w/a char("~") and should be done when updating your db(database).

To undo this, when retrieving info from db, reverse the .Replace process to TextBox1.Text.Replace(sCoolNewLine, vbNewLine) and you should get your line.breaks as previously were, in TextBox.

The char ("~") should be blocked from being typed by a user, to not confuse the way your code updates/retrieves data to/from db.

I used a String in my previously posted code, though you can always use just a char as "~" or "^", or even ".etc.". Reason for String; Not likely it will ever be typed by a user as ".:.myCooLvbNewLine.:." , thus no issues.

Hope this helps.:)

[kvprajapati]

Don't use hard-coded SQL string and no-need to add/replace NewLine characters in text which is input via Multi-line textbox.

>@kenth21v : So if I retrieve the data it displays a single lined text.

May be you are trying to display text in Single line textbox. Try using Label or Multilie text.

Code to insert row.

Using Cn As New SqlConnection("your_connection_string")
   Dim Sql = "insert into TestTable values (@mtext)"
   Using Cmd As New SqlCommand(Sql, Cn)
      Cmd.Parameters.Add("@mtext", SqlDbType.VarChar, 1000).Value = TextBox1.Text
      Cn.Open()
      Cmd.ExecuteNonQuery()
      Cn.Close()
   End Using
End Using

[kenth21v]

No I tried it in RichTextBox and Multilined TExtbox.
well I'm still trying to use your codes.

i'll post again laterr. Thanks for your help.

[kenth21v]

Dim sCoolNewLine As String = "~"
        con.Open()
        cmd = New SqlCommand("INSERT INTO SampleTbl VALUES('" & TextBox1.Text.Replace(vbNewLine, sCoolNewLine) & "'", con)
        cmd.ExecuteNonQuery()
        con.Close()

this code works perfectly!!
thanks codeorder !
thanks to everyone who response !