CODEWITHSUNDEEP
vb.net
Marc Chemali
Marc Chemali

Reputation: 109

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

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...

Upvotes: 1

Views: 1315

Answers (2)

rheitzman
rheitzman

Reputation: 2297

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.

Upvotes: 2

Andrea
Andrea

Reputation: 12375

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

Upvotes: 1

Related Questions