What am I missing to complete this stringbuilder statement?

Question

0.00/5 (No votes)

See more:

So, I am trying to make a update statement using string builder for first name, last name and middle name. problem that I am having is that its not including the comma for the values and the columns. What am I missing to complete this statement to make it work appropriately?

 Using sqlconn As New SqlConnection("SQLstatement")
            Using da As New SqlDataAdapter(String.Empty, sqlconn)
                Dim sql As New StringBuilder("insert into [")
                sql.Append(Form1.TreeView1.SelectedNode.Text.Replace("]", "\]"))
                sql.Append("] ( ")

                Dim sbOn As New StringBuilder(") values( ")
                Dim andRequired As Boolean = False

                If Not String.IsNullOrEmpty(First_NameTextBox.Text) Then
                    sql.Append(" [First_name]")
                    sbOn.Append("@Firstname")
                    If andRequired Then
                        sbOn.Append(", ")
                    End If
                    da.SelectCommand.Parameters.AddWithValue("@Firstname", 
                    First_NameTextBox.Text)
                End If

                    If Not String.IsNullOrEmpty(Last_NameTextBox.Text) Then
                    sql.Append(" [Last_name]")
                    sbOn.Append("@Lastname")
                    If andRequired Then
                        sbOn.Append(", ")
                    End If
                    da.SelectCommand.Parameters.AddWithValue("@Lastname", 
                    Last_NameTextBox.Text)
                End If


                If Not String.IsNullOrEmpty(Middlenametextbox.Text) Then
                    sql.Append(" [Middle_name]")
                    sbOn.Append("@Middlename")
                    If andRequired Then
                        sbOn.Append(", ")
                    End If

                    da.SelectCommand.Parameters.AddWithValue("@Middlename", 
                    Middlenametextbox.Text)
                End If

sql.AppendFormat(sbOn.ToString + ")")

This is what comes up when I run it with a messagebox:

"insert into [table] ([First_name] [Last_name] [Middle_name]) values(@First_name@Last_name@Middle_name)"

as you can see there is no comma, so this is an invalid sql statement.

What I have tried:

I have tried to use a boolean in the statement to help, but it's not activating the commas.

Posted 18-Aug-17 8:05am

Member 11856456

Updated 18-Aug-17 8:23am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2017-08-18T08:23:00

Try something like this:

VB.NET

If Not String.IsNullOrEmpty(First_NameTextBox.Text) Then
    If andRequired Then
        sql.Append(", ")
        sbOn.Append(", ")
    End If
    
    sql.Append("[First_name]")
    sbOn.Append("@Firstname")
    
    da.SelectCommand.Parameters.AddWithValue("@Firstname", First_NameTextBox.Text)
    andRequired = True
End If

If Not String.IsNullOrEmpty(Last_NameTextBox.Text) Then
    If andRequired Then
        sql.Append(", ")
        sbOn.Append(", ")
    End If
    
    sql.Append("[Last_name]")
    sbOn.Append("@Lastname")
    
    da.SelectCommand.Parameters.AddWithValue("@Lastname", Last_NameTextBox.Text)
    andRequired = True
End If

If Not String.IsNullOrEmpty(Middlenametextbox.Text) Then
    If andRequired Then
        sql.Append(", ")
        sbOn.Append(", ")
    End If
    
    sql.Append("[Middle_name]")
    sbOn.Append("@Middlename")
    
    da.SelectCommand.Parameters.AddWithValue("@Middlename", Middlenametextbox.Text)
    andRequired = True
End If