So Thanks to Richard I was able to allow users to click on checklistbox items and use them in the creation of an sql table inside vb.net. SO, I tried to take his awesome example and use it in a similar way. so far here is what I have:
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("]", "]]"))
For Each item In CheckedListBox1.CheckedItems
Dim columnName As String = Convert.ToString(item)
cb.AppendFormat("on T." + columnName.Replace("]", "]]") + "[{0}]S." + columnName.Replace("]", "]]"))
Next
cb.Append(")")
Dim sql As String = cb.ToString()
sqlcon.Open()
Dim cmd As SqlClient.SqlCommand
cmd = New SqlClient.SqlCommand(sql, sqlcon)
cmd.ExecuteNonQuery()
sqlcon.Close()
here is the sql statement I am trying to work into this code:
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.[age] = S.[age];
DELETE T1 FROM [table] T1 JOIN [table1] T2 ON T1.[Last Name] = T2.[Last Name] AND T1.[First Name] = T2.[First name];
I am needing help working this in. I am having a hard time with the ands and do I start a new string builder for the when matched and delete from?
What I have tried:
I have replaced multiple characters to make the sql string accurate.