Free Information Technology Magazines and eBooks

Wednesday, June 10, 2009

VB.NET: How to UPDATE record on SQL Database

This topic is part of VB.NET SQL Database Basics Tutorial series. On this article, you will learn how to incorporate SQL UPDATE statements on your VB.NET application. An SQL UPDATE statement that changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition. For this article, we will focus particularly on updating records in Microsoft SQL Server.



Note: I assumed that you already installed your SQL Server and Visual Studio .NET.

Here is the step-by-step procedure to UPDATE record on SQL server:
1. Create your VB.NET project.
2. Include the following namespaces.


Imports System.Data
Imports System.Data.SqlClient


The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.

3. Declare and instantiate your SQLConnection object and Command object as shown below


Dim con As New SqlConnection
Dim cmd As New SqlCommand

4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.


con.ConnectionString = "Data Source=atisource;Initial
Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"


5. Invoke the Open Method to connect to SQL Server.


con.Open()


6. Set the connection of the command object.


cmd.Connection = con


7. Pass the UPDATE SQL statement to the command object commandtext as shown below


cmd.CommandText = "UPDATE table SET field1 = value1, field2 = value2
WHERE field3='Test'"


8. Use the ExecuteNonQuery() method to run the UPDATE SQL statement.


cmd.ExecuteNonQuery()


The full sample source code of updating records on SQL Database:

Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = "Data Source=atisource;Initial
Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
cmd.Connection = con
cmd.CommandText = "UPDATE table SET field1 = value1, field2 = value2
WHERE field3='Test'"
cmd.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show("Error while updating record on table..." & ex.Message, "Update Records")
Finally
con.Close()
End Try





11 comments:

victor said...

sir i tried this but i got an error syntax update statements

victor said...

sir i tried this but i got an error "syntax error in update statements"
id = TextBox1.Text
productcode = TextBox2.Text
modelname = TextBox3.Text
size = TextBox4.Text
offset = TextBox5.Text
holes = TextBox6.Text
PCD = TextBox7.Text
specification = TextBox10.Text
hubore = TextBox11.Text
bolthole = TextBox8.Text
color = TextBox9.Text
colorfinishdetail = TextBox12.Text
customer = TextBox13.Text
loadsymbol = TextBox14.Text
actualload = TextBox15.Text


cmd.CommandText = "UPDATE * from wheeldatabase SET IDnumber = id, productcode = productcode, Modelname = modelname, size = size, offset = offset, holes = holes, PCD = PCD, specifications = specification, hub bore = hubore, bolt hole = bolthole, color finish = color, color finish detail = colorfinishdetail, customer = customer, loadsymbol = loadsymbol WHERE actual load = actualload"

Fryan Valdez said...

Hi victor, your UPDATE SQL syntax is incorrect. There is an "*" symbol after the UPDATE keyword

victor said...

sir na try ko na rin po na tangalin "*" itong asteris symbol but still not success.sa insert and delete ok na po ako dito nalang po ako may error sa update.
sir thanks po sa reply....

Fryan Valdez said...

You have a syntax error in the UPDATE Statement. Check it carefully. Make sure all strings are enclosed with qoute "'". If there are still error, please post the detailed error here.
thanks

victor said...

sir solved na po problem ko,thanks sa reply nyo.
I used these code :

Dim cmd = New OleDb.OleDbCommand("UPDATE [wheeldatabase] SET [productcode] = @productcode, [Modelname] = @modelname, [size] = @size, [offset] = @offset, [holes] = @holes, [PCD] = @PCD, [specifications] = @specification, [hub bore] = @hubore, [bolt hole] = @bolthole, [color finish] = @color, [color finish detail] = @colorfinishdetail, [customer] = @customer, [loadsymbol] = @loadsymbol, [actual load] = @actualload Where IDnumber = @IDnumber", Cn)
cmd.Parameters.Add(New OleDb.OleDbParameter("@ productcode", TextBox2.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@ modelname", TextBox3.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@size", TextBox4.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@offset", TextBox5.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@holes", TextBox6.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@PCD", TextBox7.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@specification", TextBox10.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@hubore", TextBox11.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@bolthole", TextBox8.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@color", TextBox9.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@colorfinishdetail", TextBox12.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@customer", TextBox13.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@loadsymbol", TextBox14.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@actualload", TextBox15.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("@IDnumber", TextBox1.Text))

ian said...

Sir Help po! ano po ba ilalagay sa button kpg mgaupdate sa SQL Database?? hind ko po kasi magawa e, tnx

ian said...

sir help po! ung code po ba ni vitor para sa ypdate button?? o para sya sa Form?? tnx!!

Anonymous said...

it worked. Thank you very much, you have been most helpful.

Anonymous said...

how do i update,edit or delete items from the existing sql database on visual basic .....

Anonymous said...

I used these code :

Dim updet As String = "update Buku set Judul = '" & Trim(txtJudul.Text) & "', Pengarang = '" & Trim(txtPengarang.Text) & "', Penerbit ='" & Trim(txtPenerbit.Text) & "', where Kode = '" & Trim(txtKode.Text) & "'"

Using conn As New OleDbConnection(My.Settings.ConnStr)
Using cmd As New OleDbCommand(updet, conn)
conn.Open()
cmd.ExecuteNonQuery()

Comment : ' Syntax error Update statement".

Help me please