Update SQL table using Vb.net

Question

I have a VB.net script that needs to put information into a table in the database. The catch is that it is based off of an item Id, so when I am updating I cannot just insert the row would like to update the row if it is already there. For this I need to use the Update command like so:

mysqlCommand = New SQLCommand(UPDATE tableName Colum2=Newvalue ... Where Column1=ItemId)

However I am wondering if there is anyway to tell wether or not a line was updated when I run

mysqlcommand.ExecuteNonQuery()

so that I do not need to check before I run this command and switch between this update command and a INSERT INTO command

macoms01 · Accepted Answer · 2014-01-21 04:17:17Z

1

I agree with @Neolisk, but a slight improvement could be to use the following:

Dim recordsAffected As Integer = mysqlcommand.ExecuteNonQuery()
If recordsAffected > 0 Then 'it worked

or this:

Dim recordsAffected As Integer = mysqlcommand.ExecuteNonQuery()
If recordsAffected >= 1 Then 'it worked

@Neolisk's code will work great as long as Column1 is a unique identifier. Otherwise you may be updating more than one row. The "greater than"/"greater than or equal to" operator will catch the scenario where you are updating more than one row.

answered Jan 21, 2014 at 4:17

macoms01

1,11013 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Victor Zakharov Over a year ago

+1. Thanks for your comment. I updated my answer, hope it looks better now.

macoms01 Over a year ago

@Neolisk - I assumed that's what you were going for. Just wanted to point it out for DoNotArrestMe in case he wasn't aware. I "upvoted" your answer as well since you were the original "responder".

Victor Zakharov · Accepted Answer · 2014-01-21 20:40:32Z

1

From documentation of SqlCommand.ExecuteNonQuery @ MSDN:

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

So you can write like this:

Dim recordsAffected As Integer = mysqlcommand.ExecuteNonQuery()
If recordsAffected = 1 Then 'it worked

EDIT: Regarding @macoms01 answer, my original intention was to have something like this:

Select Case recordsAffected
  Case 0: 'it did not work
  Case 1: 'it worked
  Case Else: Throw New BadDataException
End Select

You can never be sure that unique constraints are set on the DB - expect bad data.

But then I thought it would probably be too complicated for its purpose.

edited Jan 21, 2014 at 20:40

answered Jan 20, 2014 at 23:23

Victor Zakharov

26.6k18 gold badges95 silver badges158 bronze badges

Collectives™ on Stack Overflow

Update SQL table using Vb.net

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related