insert if update fails

[winsonlee]

I would like to update a table and it will only update if the record is found else a new record will be inserted into the table. I am not getting the results that i want with the code below. what other way can do to achieve the same result ?

Eg: weekid 3 is not found in the table, there for a new record will be inserted into the table.

        cmd.Connection = conn
        conn.Open()

        Try
            ''Query to add info into table.
            cmd.CommandText = "UPDATE    WeekNo SET weekstart = '080211', weekend = '080217' WHERE weekid = 3"
            cmd.ExecuteNonQuery()

            conn.Close()
        Catch ex As Exception
           cmd.CommandText = "INSERT INTO WeekNo(weekid, weekstart, weekend) VALUES('3','080211','080217')"
          cmd.ExecuteNonQuery()
        End Try

 

[jshultz]

The reason this is not working is because if it does not exist in the table, cmd.ExecuteNonQuery() will not throw an exception. What it will do, however, is return how many rows were affected by your query. Capture it's return value and if it is 0, run the insert statement.

if(cmd.ExecuteNonQuery() = 0)
cmd.CommandText = "INSERT INTO WeekNo(weekid, weekstart, weekend) VALUES('3','080211','080217')"
          cmd.ExecuteNonQuery()
endif