update button codes for VB.net with sql-server

Question

I have create a library management system. here if I want to update a book's particular record its updating all the records in the SQL-server database. how can I write code for update a particular record only. here is my code,

Private Sub btnedit_Click(sender As Object, e As EventArgs) Handles btnedit.Click

    con.ConnectionString = "data source=hp-pc\sqlexpress; initial catalog=Library_DB;integrated security= true"
    con.Open()
    Dim comd As New SqlCommand("update  Book set Book_Id='" & TextBox1.Text & "',Bk_Name='" & TextBox2.Text & "',Author_Name='" & TextBox3.Text & "', Year_of_release='" & TextBox4.Text & "',Availability_of_bks='" & TextBox5.Text & "'", con)
    comd.ExecuteNonQuery()
    MessageBox.Show("Updated", "Updated", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

You need to add a where cause to your SqlCommand.

Nkole Evans
– Nkole Evans

2017-11-06 05:44:08 +00:00
Commented Nov 6, 2017 at 5:44 — Nkole Evans
– Nkole Evans, Commented Nov 6, 2017 at 5:44

kiLLua · Accepted Answer · 2017-11-06 06:32:51Z

Add a WHERE clause in your SQL command to specify which book will be updated..
use the ID number of the book you want to update.
and avoid concatenating in your sql command, use parameter @

Dim comd As New SqlCommand("update  Book set Book_Id=@bookID, Bk_Name=@bkName, Author_Name=@author, Year_of_release=@release, Availability_of_bks=@avail WHERE Book_Id=@whereID", con)
comd.Parameters.Add("@bookID", SqlDbType.String).Value = TextBox1.Text
comd.Parameters.Add("@bkName", SqlDbType.String).Value = TextBox2.Text
comd.Parameters.Add("@author", SqlDbType.String).Value = TextBox3.Text
comd.Parameters.Add("@release", SqlDbType.String).Value = TextBox4.Text
comd.Parameters.Add("@avail", SqlDbType.String).Value = TextBox5.Text
comd.Parameters.Add("@whereID", SqlDbType.String).Value = "Book ID HERE"
comd.ExecuteNonQuery()
MessageBox.Show("Updated", "Updated", MessageBoxButtons.OK, MessageBoxIcon.Information)

Nkole Evans · Accepted Answer · 2017-11-06 05:46:48Z

0

You need to add a WHERE clause to your SqlCommand so that SQL Server knows what record to update. Without a WHERE clause, it will update the entire table. See below:

 con.ConnectionString = "data source=hp-pc\sqlexpress; initial catalog=Library_DB;integrated security= true"
    con.Open()
    Dim comd As New SqlCommand("update  Book set Book_Id='" & TextBox1.Text & "',Bk_Name='" & TextBox2.Text & "',Author_Name='" & TextBox3.Text & "', Year_of_release='" & TextBox4.Text & "',Availability_of_bks='" & TextBox5.Text & "' WHERE Book_Id='{**Put your book id here**}'", con)
    comd.ExecuteNonQuery()
    MessageBox.Show("Updated", "Updated", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

answered Nov 6, 2017 at 5:46

Nkole Evans

3592 silver badges10 bronze badges

3 Comments

Nkole Evans Over a year ago

If Book_Id is a number such as 10, you don't need the quotes so the query becomes WHERE Book_Id=10.

B.Nishan Over a year ago

Got it, Thanks for your answer

Nkole Evans Over a year ago

You re most welcome! Please mark as answer if that answer helped you.

Collectives™ on Stack Overflow

update button codes for VB.net with sql-server

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related