CODEWITHSUNDEEP
asp.netsql-servervb.net
zzprog
zzprog

Reputation: 113

Updating SQL database using VB.net

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

Upvotes: 0

Views: 2563

Answers (2)

Steve
Steve

Reputation: 216342

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

Upvotes: 3

RaJesh RiJo
RaJesh RiJo

Reputation: 4400

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

cm.ExecuteNonQuery()

Upvotes: 2

Related Questions