Question: Easiest way to update 1 field in db

  • Thread starter Thread starter VB Programmer
  • Start date Start date
V

VB Programmer

I have a text field with a value. What is the easiest way to update the
database with this value? I think a dataset would be overkill. Can't I
just run the UPDATE SQL command directly? Can you provide a sample?

Thanks.
 
I found 2 ways I can do it (after connection is established). Any ideas
which is best?

Dim myCommand As New SqlClient.SqlCommand( _
"UPDATE MyTable SET Password='abc' WHERE FullName = 'John
Smith'", cnnMyConnection)
myCommand.ExecuteNonQuery()

OR

With daMyDataAdapter
.SelectCommand.CommandText = "SELECT * FROM MyTable WHERE
FullName = 'John Smith';"
.Fill(dsMyDataset)
End With
With dsMyDataSet.Tables(0)
.Rows(0).BeginEdit()
.Rows(0)("Password") = "abc"
.Rows(0).EndEdit()
End With
daMyDataAdapter.Update(dsMyDataSet)
 
Which is best is totally arbitrary and depends on your
circumstances. If you only have one text box or control,
and each time it changes, you can use the simple
executenonquery method of the command objbect. If you use
this method, you'll manually open and close your
connection each time, and hopefully clean up the resources
afterward. If you use a dataset and a dataadapter,
updates won't be sent unless changes are detected. If you
only have one text box, it's not necessary and the
differences are negligible. However, if you ever decide
to use complex binding, ie the text box now holds multiple
values and you turn it into a listbox or combo or
whatever, your app won't scale very well.

Everything is a trade off and it depends on what your
needs are now and what they will be in the future.


Typically, I'd recommend the following if you are going ot
use the 'connected' method

sql = "UPDATE MyTable SET Password=@Value WHERE FullName
= 'John
Dim cn as New SqlConnection(sql)
Dim cmd as New SqlCOmmand(sql, cn)
cmd.Parameters.Clear
cmd.Parameters.Add("@Value", textbox.text)
Try
If cn.State <> ConnectionState.Open Then cn.Open
Catch ex as SqlConnection
Messagebox.Show("Connection Failed")
end try

try
cmd.ExecuteNonQuery
catch

finally
if cn.State <> Connection.Closed then cn.close

end try

Good Luck,

Bill

W.G. Ryan
(e-mail address removed)
 
Back
Top