Click here to Skip to main content
15,920,664 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 29-Jan-17 17:25pm

1 solution

If I understand your question correctly, you are facing problem adding operators in query. The possible solution would be taking Joins / where clause in separate string builder and then verifying the length of same to decide if any operator needs to be added or not.

I hope you are no taking table and field names directly from UI. If you are then please look for SQL injection. Sample is available here[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900