CODEWITHSUNDEEP
sqlvb.netstringstringbuilder
JTunney
JTunney

Reputation: 904

Remove last comma in StringBuilder SQL Query

I am creating a SQL Update Query using String Builder and if there is a comma at the end I need to remove it.

For example:

 Dim query As New StringBuilder
 query.AppendLine("Update Vendor")
 If Not String.IsNullOrEmpty(vendInfo.Name) Then
      query.AppendLine("Set VendorName= @VendorName, ")
 End If
 If Not String.IsNullOrEmpty(vendInfo.VendorType) Then
      query.AppendLine("VendorType= @VendorType, ")
 End If

The query errors on the last item since there will always be a comma. Once I am done going through all of my If statements I want to remove a comma from the end.

Any ideas?

Upvotes: 0

Views: 1553

Answers (2)

Steve
Steve

Reputation: 216313

Just decrease the Length property of the StringBuilder

query.Length -= 2

However, beware of your UPDATE query. The WHERE clause seems to be missing and a space is required between the TableName and the SET instruction.
Also another problem happens if you don't have a Vendinfo.Name. You exclude the code that insert the SET but this is required for the first field of your update

 Dim query As New StringBuilder
 query.AppendLine("Update Vendor SET ")
 If Not String.IsNullOrEmpty(vendInfo.Name) Then
      query.AppendLine("VendorName= @VendorName, ")
 End If
 If Not String.IsNullOrEmpty(vendInfo.VendorType) Then
      query.AppendLine("VendorType= @VendorType, ")
 End If

 If query.Length > 18 Then
    ' 18 chars are fixed in UPDATE Vendor SET'
    query.Length -= 2
    ' add the WHERE clause'
    ......
 End If

Upvotes: 3

user1228
user1228

Reputation:

Easiest way is to create a collection with each variable (sorry, c# here)

var list = new List<string>();
// add your vars
if(!string.IsNullOrEmpty(vendInfo.Name))
      list.Add("Set VendorName= @VendorName");
// and join them
var vars = string.Join(", ", list);

The secret is String.Join, which easily concats an enumerable of strings, placing a delimiter between each (but not at the end).

Upvotes: 4

Related Questions