Splitting a string of text from textbox using VB.NET

Question

I want to add a text box on a web page in order for users to add space delimited tags. When the user submits the page how do I take the tags out of the text box and add each as a record in a SQL database?

I want to use VB.NET.

Answer 1

Sounds like you want something like:

Using connection as New SqlConnection("connection string")
    connection.Open()

    For Each tag As String In TextBox.Split(CChar(" "))
        Using command as New SqlCommand("INSERT INTO UsedTags(Tag) VALUES(@Tag)", connection)
            command.Parameters.Add("@Tag", SqlDbType.varchar).value = tag

            Try
                command.ExecuteNonQuery()
            Catch ex As SqlException
                Debug.WriteLine(ex.ToString())
            End Try
        End Using
    End For
End Using

Not tested, but should be close enough to your needs.

Answer 2

My way would be to send the value from the Text Box to a SQL Stored Procedure, have that SPLIT the text, and then insert the individual rows.

All this will be set-based, so very fast.

if you split it in VB.NET and send individual INSERT statements to SQL that will be much slower.

You could convert the Text Box delimited data into XML and send that to SQL, and use that as a basis for your INSERT statements. But personally I think Splitting it in SQL is preferable.

I'll find a link for you

I didn't find a worked example easily, so here's a suggestion

INSERT INTO UsedTags
(
    Tag
)
SELECT SplitValue
FROM dbo.MySplitFunction(@ListOfTags)

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 for recommended SPLIT functions

If you want to just make an INSERT direct from VB.NET you could do something like:

INSERT INTO UsedTags
(
    Tag
)
SELECT "Tag1"
UNION ALL
SELECT "Tag2"

i.e. you generate the compound SELECT statement in VB.NET having split the delimited tag string.

(If there might be duplicate tags, and you want to remove duplicates, use "UNION" instead of "UNION ALL")