如何将richtextbox行解析为SQL [英] How to parse richtextbox lines into SQL

问题描述

到目前为止，我只能做一行，我希望能够多行。我希望它占用每一行并将其与文本进行比较，因此如果程序找到Last Name：，First Name：，Sex：，Race：它将获取这些后面的字符并将其应用于特定的sql数据库单元格。到目前为止它只会只分配姓氏：但我有第一个名字：试图提取不同的信息。此外，我想把它放在文本不一定区分大小写的地方。



这就是我所拥有的：

So far I am only able to do just one line, I want to be able to multiple lines. I want it to take each line and compare it to the text, so if the program locates Last Name:, First Name:, Sex:, Race: it will take the characters after these and apply it to a specific sql database cell. so far it will only allocate just the Last Name:, but I have the first name: trying to pull different information. Also I would like to make it where the text does not have to be case sensitive.

here is what I have:

Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load


        RichTextBox1.Text = "last Name: Samson
First Name: Jeff
Race: W
Sex: M"

        Try

            Dim sqlcon As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\users\jj\documents\visual studio 2015\Projects\WindowsApplication3\WindowsApplication3\Database1.mdf;Integrated Security=True")
            Dim sqladapt = New SqlDataAdapter("Select * from " + "[" + Form1.TreeView1.SelectedNode.Text.ToString, sqlcon)

            sqlcon.Open()
            Dim cmd As SqlClient.SqlCommand
            Dim sql As String = "insert into " + "[" + Form1.TreeView1.SelectedNode.Text.ToString + "]" + "values(@id,@Last,@First,@Sex,@Race)"
            cmd = New SqlClient.SqlCommand(sql, sqlcon)

            ' Make sure that all tables have the same type of information that can be entered, if not you will recieve an error.
            cmd.Parameters.AddWithValue("@id", Form1.IdTextBox.Text)


            cmd.Parameters.AddWithValue("@Last", RichTextBox1.Text.Replace("last Name: ", RichTextBox1.Text.Substring(11)))




            cmd.Parameters.AddWithValue("@First", RichTextBox1.Text.Replace("First Name: ", RichTextBox1.Text.Substring(12)))
            cmd.Parameters.AddWithValue("@Sex", Form1.SexTextBox.Text())
            cmd.Parameters.AddWithValue("@Race", Form1.RaceTextBox.Text)



            cmd.ExecuteNonQuery()
            sqlcon.Close()

            MessageBox.Show("New Record Added")
        Catch ex As Exception
            MsgBox(ex.Message)

            End Try



    End Sub

我尝试过：



我试过使用substring函数把我带到特定的文本。另外，我试图做单独的if语句，但它一直告诉我必须分配一个值。

What I have tried:

I have tried just to use the substring function to take me to specific text. also, I have tried to do separate if statements but it keeps telling me that I have to assign a value.

推荐答案

从自由格式文本中提取有意义的值是众所周知的很难。



如果你完全确定每个值都在它自己的行上，那么该行将以正确的标签开头，标签和值将是用冒号分隔，然后可能能够使用正则表达式 [ ^ ]来提取值。

Extracting meaningful values from free-form text is notoriously difficult.

If you're absolutely certain that each value will be on its own line, that the line will start with the correct label, and the label and value will be separated with a colon, then you might be able to use a Regular Expression[^] to extract the values.

Dim lastName As String = Nothing, firstName As String = Nothing, race As String = Nothing, sex As String = Nothing

Dim pattern As New Regex("^\s*(?<label>[^:]+)\s*:\s*(?<value>.+)\s*\r?

，RegexOptions.Multiline）

Dim 匹配 As MatchCollection = pattern.Matches（RichTextBox1.Text）

对于 每个匹配 As 匹配在中匹配
Dim 标签作为 字符串 = match.Groups（ label）。值
如果 String .Equals(label， 姓氏，StringComparison.OrdinalIgnoreCase）然后
lastName = match.Groups（ value）。值
其他 如果 String .Equals(label,< span class =code-string> 名字，StringComparison.OrdinalIgnoreCase）然后
firstName = match.Groups（ value）。价值
Els e 如果 字符串 .Equals（label， Race，StringComparison.OrdinalIgnoreCase）然后
race = match.Groups （ value）。值
Else 如果 字符串 .Equals（label， 性别，StringComparison.OrdinalIgnoreCase）然后
sex = match.Groups（ value）。值
结束 如果
下一步

", RegexOptions.Multiline) Dim matches As MatchCollection = pattern.Matches(RichTextBox1.Text) For Each match As Match In matches Dim label As String = match.Groups("label").Value If String.Equals(label, "Last Name", StringComparison.OrdinalIgnoreCase) Then lastName = match.Groups("value").Value Else If String.Equals(label, "First Name", StringComparison.OrdinalIgnoreCase) Then firstName = match.Groups("value").Value Else If String.Equals(label, "Race", StringComparison.OrdinalIgnoreCase) Then race = match.Groups("value").Value Else If String.Equals(label, "Sex", StringComparison.OrdinalIgnoreCase) Then sex = match.Groups("value").Value End If Next

这篇关于如何将richtextbox行解析为SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！