Updating SQL database using VB.net

Question

I try to update database in MS SQL in ASP using VB.NET. This is my code. There is no error but the data in database are not updated

Dim cnConnect As New SqlConnection
    cnConnect.ConnectionString = conn.ConnectionString
    cnConnect.Open()
    Dim cm As New SqlCommand
    cm.CommandText = "UPDATE book SET name = '" & name.Text & "'
    WHERE idnum = '" & idno.Text & "';"
    cm.Connection = cnConnect
    cnConnect.Close()

A command needs to be executed to produce any result. Your code lacks a critical line: cm.ExecuteNonQuery — Steve, Oct 05 '16 at 09:20
By the way, you've got this existing connection called conn - you could probably have used that as the connection to attach to the command, not needed to make a new connection. — Cato, Oct 05 '16 at 10:00

score 3 · Answer 1 · edited May 23 '17 at 12:19

A command needs to be executed to produce any result.
You need to call cm.ExecuteNonQuery.
However your code has other problems.
The most dangerous one is the string concatenation of values to form an Sql Statement. This is a well known problem that allows hackers to mess with (or simply destroy) your database.
Apart from hacking, also a simple quote inside your name TextBox (like McDonald's) will break your code with a syntax error. (try it)
There is only one solution and it is: Use Parameters

Dim sqlText = "UPDATE book SET name = @name WHERE idnum = @num"
Using cnConnect = New SqlConnection(conn.ConnectionString)
Using cm = new SqlCommand(sqlText, cnConnect)
   cnConnect.Open()
   cm.Parameters.Add("@name", SqlDbType.NVarChar).Value = name.Text
   cm.Parameters.Add("@num", SqlDbType.NVarChar).Value = idno.Text 
   cm.ExecuteNonQuery()
End Using
End Using

Also note how the Disposable objects used are enclosed in a Using block to allow the automatic destroy of the resources used by those objects at the closing of the Using block

score 2 · Accepted Answer · answered Oct 05 '16 at 09:23

2

I think you missed to execute the query. You may have to execute the query before closing the connection like this,

cm.ExecuteNonQuery()

answered Oct 05 '16 at 09:23

RaJesh RiJo

4,302
4
25
46

Updating SQL database using VB.net

2 Answers2