CODEWITHSUNDEEP
sql-servervb.net
Taha Khan
Taha Khan

Reputation: 23

Update SQL Server database dynamically in VB.Net

I'm trying to generate a query dynamically using textbox values from my Bookings form to update only those values that were entered by the user.

I am using the following code:

Dim str As String

str = "UPDATE Bookings SET "
Dim first As Integer = 1
For Each x As Control In Me.Controls
    If x.GetType Is GetType(TextBox) Then
        If first = 1 Then
            first = 2
        Else
            str &= ","
        End If
        If x.Tag = 1 Then
            str = str & x.Name & " = @" & x.Name
        End If
    End If
Next

But it is generating the query like this:

Update Bookings SET ,,booking_date = @booking_date,,,,,cust_name = @cust_name where bookingID = @bookingID

Or if I want to update just 1 field it generates this:

Update Bookings SET ,,,,,,,cust_name = @cust_name where bookingID = @bookingID

Upvotes: 1

Views: 1159

Answers (1)

chrissie1
chrissie1

Reputation: 5029

Dim str As String  
str = "UPDATE Bookings SET "
Dim comma As string = ""
For Each x As Control In Me.Controls
  If x.GetType Is GetType(TextBox) Then
    If x.Tag = 1 Then
      str &= comma & x.Name & " = @" & x.Name
      comma = ","
    End If
  End If
Next

And here is the One line answer.

Dim str = "UPDATE Bookings SET " & String.Join(",", (From _E In Controls.OfType(Of Control)() Where _E.GetType() Is GetType(TextBox) AndAlso _E.Tag = "1" Select _E.Name).ToList())

Upvotes: 1

Related Questions