how can I get RichTextBox data into my sql server table using?

Question

I have a RichTextBox which has only text. How can I get the data (data is tab delimited) into my sql server table ? (I am not forced to use RTB so any other options would do fine as long as the entire process will be quick)

Example of text in RTB:

John 1985 (tab) 01 (tab) 19 (tab) 1.80 (tab) 70

Tony 1988 (tab) 02 (tab) 27 (tab) 1.67 (tab) 55

Table in sql Server called "Users":

Name, Year, Month, Date, Height, Weight

I found this code Dim lines As String() = myRichTextbox.Text.Split(New Char() {"\n"c}) so i just need to get each part of every line into a specific column...

any ideas ? I can't seem to get the data from RTB into SQL SERVER table. And i can't find it online... i will loop this process every 10 minutes so i don't want to save the RTB text into a file and then read from that file to save the data in table...

note that Split(New Char() {"\n"c}) gives me an error in VS... if you truly want a newline use {"\n"} and if you want to add each tabbed field to the array use Split(New Char() {vbTab}) — maxedev, Jun 12 '13 at 16:45
The syntax `\n` is for C# where the backslash "escapes" the following character. You could use myRichTextBox.Lines to get the lines, or if it genuinely is split by newline characters and not carriage return, newline, you can split on Chr(10). — Andrew Morton, Jun 12 '13 at 17:38
There are many ways to accomplish this. Here is one: [How to read a csv file into a .net datatable](http://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable). Then use [SqlBulkCopy](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx). — Victor Zakharov, Jun 13 '13 at 00:38

score 2 · Answer 1 · answered Jun 12 '13 at 21:36

Once you have parsed the data you can use dynamic SQL or a stored procedure to insert the data into the DB.

SQL server manager can build the INSERT syntax for your table e.g.:

            INSERT INTO [OIS].[dbo].[Burn_Type]
                       ([Burn_Type]
                       ,[Record_Create_Date]
                       ,[Last_Update_Date]
                       ,[Burn_Fee])
                 VALUES
                       (<Burn_Type, varchar(40),>
                       ,<Record_Create_Date, datetime,>
                       ,<Last_Update_Date, datetime,>
                       ,<Burn_Fee, float,>)

You would need to replace the VALUES with your data then execute the SQL, here is some general code:

    Public Function UpdateBySQL(ByVal SQL As String, Optional ByVal UserConnectString As String = "") As Integer
    Dim sConStr As String = UserConnectString
    If sConStr.Length = 0 Then sConStr = g.OISConnectString
    Dim cmd As New Data.SqlClient.SqlCommand ' text commands only
    Dim iCnt As Integer
    Try
        ' wouldn't work for transactions
        Using con As New SqlConnection(sConStr)
            con.Open()
            cmd.CommandText = SQL
            cmd.Connection = con
            iCnt = cmd.ExecuteNonQuery()
            con.Close()
        End Using
    Catch ex As Exception
        MsgBox(ex.Message & vbCrLf & vbCrLf & SQL)
    End Try
    Return iCnt
End Function

For higher security and performance use stored procedures.

Andrea · Accepted Answer · 2013-06-12T19:50:52.663

1

You can split on the new line character (chr(10)) to get the rows, and then on each row you can split on tab character (chr(9)) to get the "columns"

Dim rows As String() = source.Split(Chr(10))
Dim columns As String()

For Each line As String In rows
   columns = line.Split(Chr(9))
Next

edited Jun 12 '13 at 19:50

answered Jun 12 '13 at 15:54

Andrea

11,801
17
65
72

how can I get RichTextBox data into my sql server table using?

2 Answers2