Duplicate SQL statement into a usuable form

Question

0.00/5 (No votes)

See more:

I am trying to use my checklist boxes to fill in T. or S. fields by what is selected. This is my first time of attempting to use append format. I am needing some help figuring this out. I have placed the original SQL statement below.

VB

Private Sub sompare_other_tables()

       Dim sqlcon As New SqlConnection("sqlstatement")

       Dim cb As New System.Text.StringBuilder("MERGE INTO")
       Dim tableName As String = ListBox1.SelectedItems(Convert.ToString(tableName))
       Dim tablename2 As String = ListBox2.SelectedItems(Convert.ToString(tablename2))
       cb.AppendFormat(" [{0}] ", tableName.Replace("]", "]]"))
       cb.Append("as T")
       cb.AppendFormat(" using [{0}] ", tablename2.Replace("]", "]]"))
       cb.Append("As S ")
       '  cb.AppendFormat(", [{0}] nvarchar(max) NULL", tableName.Replace("]", "]]"))
       cb.AppendFormat("on T.", tableName.Replace("]", "]]"))
       cb.Append(Convert.ToString(" = "))
       cb.Append("[{0}] S.", tablename2.Replace("]", "]]"))

       For Each item In CheckedListBox1.CheckedItems
           Dim columnName As String = Convert.ToString(item)
           cb.AppendFormat("on T." + columnName.Replace("]", "]]"))
           cb.Append(" = ")
           cb.Append("S." + columnName.Replace("]", "]]"))

       Next

       Dim sql As String = cb.ToString()

       sqlcon.Open()
       Dim cmd As SqlClient.SqlCommand
       cmd = New SqlClient.SqlCommand(sql, sqlcon)

       MessageBox.Show(cmd.CommandText)

       cmd.ExecuteNonQuery()
       sqlcon.Close()

This is the original SQL statement:

SQL

Merge into table1 as T using [table] as S on T.[Last Name] = S.[Last Name] and T.[First Name] = S.[First Name] 
When Matched then Update Set T.[Birth] = S.[Birth];

DELETE T1 FROM [table] T1 JOIN [table1] T2 ON T1.[Last Name]  = T2.[Last Name] AND T1.[First Name]  = T2.[First name];

What I have tried:

Haven't tried to much, since I don't know much about appends. if there is a better way to write the statement I would love to hear it.

Posted 29-Mar-17 23:19pm

Member 11856456

Updated 30-Mar-17 2:07am

Andy Lanng

v2

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)

CHill60 · Accepted Answer · 2017-03-30T02:07:00

If you have a look at the line

VB

cb.AppendFormat("on T.", tableName.Replace("]", "]]"))

You have not put a placeholder in the string for the value you are using. I would expect something like

VB

cb.AppendFormat("on T.{0}", tableName.Replace("]", "]]"))

Similarly, you have used string concatenation in the line

VB

cb.AppendFormat("on T." + columnName.Replace("]", "]]"))

so there was no point in using AppendFormat, you could have just used Append. Avoid string concatenation and do this instead:

VB

cb.AppendFormat("on T.{0}", columnName.Replace("]", "]]"))

Now consider the line

VB

cb.Append(Convert.ToString(" = "))

There is no need for the Convert.ToString()... " = " is already a string

When using AppendFormat you can include several variables in the format list to make the code a little tidier. For example you could replace

VB

        cb.AppendFormat(" [{0}] ", tableName.Replace("]", "]]"))
cb.Append("as T")
cb.AppendFormat(" using [{0}] ", tablename2.Replace("]", "]]"))
cb.Append("As S ")

with

VB

cb.AppendFormat(" [{0}] as T using [{1}] as S", tableName.Replace("]", "]]"), tablename2.Replace("]", "]]"))

Without the benefit of any of the data in your list boxes it's a little difficult to comment on the rest of it. For example it is unclear why you are replacing ] with ]] etc

As the ON clause is repeated in both the update and the delete sql I would create that as a separate StringBuilder so you can re-use it.

This is the code I came up with, but you would have to check the output to make sure it is correct after plugging in your data from the list controls:

VB

Dim cb2 As New StringBuilder("MERGE INTO ")
  cb2.AppendFormat("{0} as T using {1} as S", tableName, tablename2)

  Dim sbOn As New StringBuilder(" ON ")
  Dim andRequired As Boolean = False
  For Each item In CheckedListBox1.CheckedItems
      If andRequired Then
          sbOn.Append(" AND ")
      End If
      Dim columnName As String = item.ToString()
      sbOn.AppendFormat("on T.[{0}] = S.[{1}]", columnName, columnName)
      andRequired = True
  Next
  cb2.Append(sbOn.ToString())
  cb2.Append("When Matched then Update Set T.[Birth] = S.[Birth];")

  cb2.AppendFormat("DELETE T1 FROM [{0}] T1 JOIN [{1}] T2", tablename2, tableName)
  cb2.Append(sbOn.ToString())

  Debug.Print(cb2.ToString())

Note the trick I've used to make sure I don't get an extra AND in the ON clause