Update SQL Server database dynamically in VB.Net

Question

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

I know this does not answer your question at all, but please consider using SQLCommand and SQLParameters if this code is meant for production use. Generating SQL like that is a massive security flaw :) — Daryl Teo
– Daryl Teo, Commented Sep 19, 2011 at 17:35
I've used sqlcommand and sql parameters and this code is just to generate a query at run time for textboxes having a value — Taha Khan
– Taha Khan, Commented Sep 19, 2011 at 23:07

chrissie1 · Accepted Answer · 2011-09-19 11:13:45Z

1

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())

edited Sep 19, 2011 at 11:13

answered Sep 19, 2011 at 10:40

chrissie1

5,0293 gold badges28 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taha Khan Over a year ago

I have 2 masked text boxes 1 is cust_id and its mask is "csc- " i.e. four integer values are required after csc- and the other one is contact whose mask is " - " i.e. four integer values are required before '-' and 7 integer values are required after '-'......But both these textboxes are not getting validated and i have put .tag = 1 at validated event and i have also tried it at textchanged event but useless and the query is not changed for these if some value is entered in these 2 textboxes...Please suggest a remedy for this

Collectives™ on Stack Overflow

Update SQL Server database dynamically in VB.Net

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related